Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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!
@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
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.
Hi Sim,
Have you got any solution for expanding Lists and Records from JSON file dynamically?
Any help is greatly appreciated.
Thanks in advance!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
7 | |
6 | |
5 |