Expand all the columns in JSON dynamically using Power Query (M language)

[Anonymous]

I am having a JSON as follows:

{
"Team": [
    {
        "TeamId": "1",
        "TeamName": "Chicago Bulls",
        "TeamPlayers": [
            {"PlayerId": "1", "PlayerName": "Pau Gasol"},
            {"PlayerId": "2", "PlayerName": "Derrick Rose"},
            {"PlayerId": "3", "PlayerName": "Joakim Noah"},
            {"PlayerId": "4", "PlayerName": "Jimmy Butler"},
            {"PlayerId": "5", "PlayerName": "Taj Gibson"}
        ]
    },
    {
        "TeamId": "2",
        "TeamName": "Cleveland Cavaliers",
        "TeamPlayers": [
            {"PlayerId": "1", "PlayerName": "Lebron James"},
            {"PlayerId": "2", "PlayerName": "Kyrie Irving"},
            {"PlayerId": "3", "PlayerName": "Anderson Varejao"},
            {"PlayerId": "4", "PlayerName": "Dion Waiters"},
            {"PlayerId": "5", "PlayerName": "Shawn Marion"}
        ]
    },
    {
        "TeamId": "3",
        "TeamName": "Los Angeles Clippers",
        "TeamPlayers": [
            {"PlayerId": "1", "PlayerName": "Chris Paul"},
            {"PlayerId": "2", "PlayerName": "Blake Griffin"},
            {"PlayerId": "3", "PlayerName": "DeAndre Jordan"},
            {"PlayerId": "4", "PlayerName": "Jamal Crawford"},
            {"PlayerId": "5", "PlayerName": "Matt Barnes"}
        ]
    }
]
}

The number of columns in this JSON file can be changed along with the content it holds.

For example: There may be an addition to new columns containing lists or records in them, or there may be a decrease in number of the columns in many JSON files required further.

I want to write a code in M language in such a way that, I could be able to expand all the columns in the JSON file at once, including the lists and records, irrespective of the number/name/content of the column.

This has to happen dynamically where column names are unknown (i.e., the next file may have 10 number of columns with lists and records in any/all of the columns).

So the above JSON file should be something as follows, but dynamically: 

Any help is greatly appreciated.

Thanks in advance!

[v-yuezhe-msft]

@Anonymous,

Create a function in Power BI Desktop as described in this blog.

Then invoke this function as below.

let
    Source = FunctionName("{
""Team"": [
    {
        ""TeamId"": ""1"",
        ""TeamName"": ""Chicago Bulls"",
        ""TeamPlayers"": [
            {""PlayerId"": ""1"", ""PlayerName"": ""Pau Gasol""},
            {""PlayerId"": ""2"", ""PlayerName"": ""Derrick Rose""},
            {""PlayerId"": ""3"", ""PlayerName"": ""Joakim Noah""},
            {""PlayerId"": ""4"", ""PlayerName"": ""Jimmy Butler""},
            {""PlayerId"": ""5"", ""PlayerName"": ""Taj Gibson""}
        ]
    },
    {
        ""TeamId"": ""2"",
        ""TeamName"": ""Cleveland Cavaliers"",
        ""TeamPlayers"": [
            {""PlayerId"": ""1"", ""PlayerName"": ""Lebron James""},
            {""PlayerId"": ""2"", ""PlayerName"": ""Kyrie Irving""},
            {""PlayerId"": ""3"", ""PlayerName"": ""Anderson Varejao""},
            {""PlayerId"": ""4"", ""PlayerName"": ""Dion Waiters""},
            {""PlayerId"": ""5"", ""PlayerName"": ""Shawn Marion""}
        ]
    },
    {
        ""TeamId"": ""3"",
        ""TeamName"": ""Los Angeles Clippers"",
        ""TeamPlayers"": [
            {""PlayerId"": ""1"", ""PlayerName"": ""Chris Paul""},
            {""PlayerId"": ""2"", ""PlayerName"": ""Blake Griffin""},
            {""PlayerId"": ""3"", ""PlayerName"": ""DeAndre Jordan""},
            {""PlayerId"": ""4"", ""PlayerName"": ""Jamal Crawford""},
            {""PlayerId"": ""5"", ""PlayerName"": ""Matt Barnes""}
        ]
    }
]
}")
in
    Source

Regards,
Lydia

[Anonymous]

I have gone through the blog referred by you earlier, but it did not help much, as the need was to expand the columns dynamically.

Also, when I had tried out the same blog practically, it gave me an expanded table as follows:

 

Hence, I am not able to refer to any help online, that can do dynamic expansion of JSON without knowing the names of the columns, count of the columns or the content in it (which can be list/records/simple text). 

Also, I am new to Power Query and it is difficult to tweak the code as per the necessity.

Any help is appreciated.

[Anonymous]

Hi Sim,

Have you got any solution for expanding Lists and Records from JSON file dynamically?

Any help is greatly appreciated.

Thanks in advance!