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
                        }
                    }
                ]
            }

        }]

Answer 1

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"