Json Array Query-Power BI

Question

Hi i have the below JSON, would like to extract in PowerBI Query. My query is not able to extract Array's inside the JSON. I am unable to extract properties array values, where as i am able to extract user values.

Any help appreciated

Edit1: Added additional column Renames and achieved result based on @AnkUser solution

Edit2: Below JSON

I would like form the power query to return as

Workers      WorkCode    Place
-----------------------
Manager       134         UK
delegate      135         Europe 
Authority     etc

There is no relationship between these columns. However, they will be used as additional filter data for the previous Query Sample JSON

{
  "Data": [
    {
      "Type": "Workers",
      "Values": [
        "Manager",
        "Delegate",
        "Authority"
      ]
    },
    {
      "Type": "WorkCode",
      "Values": [
        "134",
        "135",
        "140",
        "141",
        "142",
        "143",
        "150"
      ]
    },
    {
      "Type": "Place",
      "Values": [
        "UK",
        "Europe"
      ]
    }
  ]
}

Below Sample power query:

let
    Source = Json.Document(Web.Contents("http:localhost")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"User", "Properties"}, {"Column1.User", "Column1.Properties"}),
    #"Expanded Column1.User" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.User", {"recId", "Description", "Type", }, {"Column1.User.recId", "Column1.User.Description", "Column1.User.Type"}),
    #"Expanded Column1.Properties" = Table.ExpandListColumn(#"Expanded Column1.User", "Column1.Properties"),
    #"Expanded Column1.Properties1" = Table.ExpandRecordColumn(#"Expanded Column1.Properties", "Column1.Properties", {"PersonID", "HomeRef", "Designation", "EstateAgent", "Mortgage", "Broker", "Citizen"}, {"Column1.Properties.PersonID", "Column1.Properties.HomeRef", "Column1.Funds.Designation", Column1.Properties.EstateAgent", Column1.Properties.Mortgage", Column1.Properties.Broker",Column1.Properties.Citizen"})
    )
in
    #"Expanded Column1"

Sample data:

    [
        {
            "User": {
                "recId": "0154911",
                "Description": "Lindsay Properties ltd",
                "Type": "Organisation",
                "Properties": [
                    {
                        "PersonID": 5636,
                        "HomeRef": 149065,
                        "Designation":"Owner",
                        "EstateAgent": {
                            "Code": "8533",
                            "Description": "Hunters-properties"
                        },
                        "Mortgage": {
                            "Code": "natwide",
                            "Description": "Bank limited"
                        },
                        "Broker": {
                            "Description": "Managecentre"
                        },
                        "Citizen": {
                            "UK": true,
                            "USA": false,
                            "Europe": false
                        }
                    },
                    {
                        "PersonID": 5636,
                        "HomeRef": 149066,
                        "Designation":"Owner",
                        "EstateAgent": {
                            "Code": "8533",
                            "Description": "Hunters-properties"
                        },
                        "Mortgage": {
                            "Code": "natwide",
                            "Description": "Bank limited"
                        },
                        "Broker": {
                            "Description": "Managecentre"
                        },
                        "Citizen": {
                            "UK": false,
                            "USA": false,
                            "Europe": false
                        }
                    }
                ]
            }
        },


   {
            "User": {
                "recId": "0154912",
                "Description": "Mr Mortimier properties",
                "Type": "Person",
                "Properties": [
                    {
                        "PersonID": 1636,
                        "HomeRef": 199065,
                        "Designation":"Owner",
                        "EstateAgent": {
                            "Code": "9533",
                            "Description": "Whitegates-properties"
                        },
                        "Mortgage": {
                            "Code": "Yoskhire society",
                            "Description": "society limited"
                        },
                        "Broker": {
                            "Description": "Managecentre"
                        },
                        "Citizen": {
                            "UK": true,
                            "USA": true,
                            "Europe": false
                        }
                    },
                    {
                        "PersonID": 1636,
                        "HomeRef": 199066,
                        "Designation":"Authority",
                        "EstateAgent": {
                            "Code": "9533",
                            "Description": "Whitegates-properties"
                        },
                        "Mortgage": {
                            "Code": "Yoskhire society",
                            "Description": "society limited"
                        },
                        "Broker": {
                            "Description": "Managecentre"
                        },
                        "Citizen": {
                            "UK": true,
                            "USA": true,
                            "Europe": false
                        }
                    }
                ]
            }

        }]

AnkUser · Accepted Answer · 2019-08-14 10:16:48Z

If I understand your question correctly, You want your array from propeties to be expanded as column for a Row. To Test your use case I have used your data and I tried to create rows from it. Below screenshot is the result.

If this is what you need, below is the query I got from PowerBI which gives result.

Note: You might want to clean Names of column.

let
    Source = Json.Document(File.Contents("C:\Users\achikhale\Desktop\stackoverflowPowerBIJson.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"User"}, {"Column1.User"}),
    #"Expanded Column1.User" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.User", {"recId", "Description", "Type", "Properties"}, {"Column1.User.recId", "Column1.User.Description", "Column1.User.Type", "Column1.User.Properties"}),
    #"Expanded Column1.User.Properties" = Table.ExpandListColumn(#"Expanded Column1.User", "Column1.User.Properties"),
    #"Expanded Column1.User.Properties1" = Table.ExpandRecordColumn(#"Expanded Column1.User.Properties", "Column1.User.Properties", {"PersonID", "HomeRef", "Designation", "EstateAgent", "Mortgage", "Broker", "Citizen"}, {"Column1.User.Properties.PersonID", "Column1.User.Properties.HomeRef", "Column1.User.Properties.Designation", "Column1.User.Properties.EstateAgent", "Column1.User.Properties.Mortgage", "Column1.User.Properties.Broker", "Column1.User.Properties.Citizen"}),
    #"Expanded Column1.User.Properties.EstateAgent" = Table.ExpandRecordColumn(#"Expanded Column1.User.Properties1", "Column1.User.Properties.EstateAgent", {"Code", "Description"}, {"Column1.User.Properties.EstateAgent.Code", "Column1.User.Properties.EstateAgent.Description"}),
    #"Expanded Column1.User.Properties.Mortgage" = Table.ExpandRecordColumn(#"Expanded Column1.User.Properties.EstateAgent", "Column1.User.Properties.Mortgage", {"Code", "Description"}, {"Column1.User.Properties.Mortgage.Code", "Column1.User.Properties.Mortgage.Description"}),
    #"Expanded Column1.User.Properties.Broker" = Table.ExpandRecordColumn(#"Expanded Column1.User.Properties.Mortgage", "Column1.User.Properties.Broker", {"Description"}, {"Column1.User.Properties.Broker.Description"}),
    #"Expanded Column1.User.Properties.Citizen" = Table.ExpandRecordColumn(#"Expanded Column1.User.Properties.Broker", "Column1.User.Properties.Citizen", {"UK", "USA", "Europe"}, {"Column1.User.Properties.Citizen.UK", "Column1.User.Properties.Citizen.USA", "Column1.User.Properties.Citizen.Europe"})
in
    #"Expanded Column1.User.Properties.Citizen"

If this is what you need I could add some more explanation (steps) on how I achieved this model of data

Edit: New query for Data Note: your Json

let
    Source = Json.Document(File.Contents("C:\Users\achikhale\Desktop\stackoverflowPowerBIJson1.json")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"Type", "Values"}, {"Value.Type", "Value.Values"}),
    #"Expanded Value.Values" = Table.ExpandListColumn(#"Expanded Value1", "Value.Values")
in
    #"Expanded Value.Values"

But if I edit your Json as below

[{
        "Data": [{
                "Type": "Workers",
                "Values": [
                    "Manager",
                    "Delegate",
                    "Authority"
                ]
            }, {
                "Type": "WorkCode",
                "Values": [
                    "134",
                    "135",
                    "140",
                    "141",
                    "142",
                    "143",
                    "150"
                ]
            }, {
                "Type": "Place",
                "Values": [
                    "UK",
                    "Europe"
                ]
            }
        ]
    }
]

Then you will get more clean Table and it's rows with below query.

Note below query will only work with my edited Json mentioned above.

let
    Source = Json.Document(File.Contents("C:\Users\achikhale\Desktop\stackoverflowPowerBIJson1.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Data"}, {"Column1.Data"}),
    #"Expanded Column1.Data" = Table.ExpandListColumn(#"Expanded Column1", "Column1.Data"),
    #"Expanded Column1.Data1" = Table.ExpandRecordColumn(#"Expanded Column1.Data", "Column1.Data", {"Type", "Values"}, {"Column1.Data.Type", "Column1.Data.Values"}),
    #"Expanded Column1.Data.Values" = Table.ExpandListColumn(#"Expanded Column1.Data1", "Column1.Data.Values")
in
    #"Expanded Column1.Data.Values"

That's Brill Worked out for me, though i will need another simple JSON array values to be converted to Column names actually, i will edit the question
Cool. Could you mark it as solved, will help others as well. Sure update the Json will update answer as well.
Can you please once again check my expected output please ? i guess this needs pivot, type should become column name

Collectives™ on Stack Overflow

Json Array Query-Power BI

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related