Forum Discussion
Multi-Dimensional Json file to Table
Hi aurischa，
Please follow the following steps to import your JSON file to Power BI.
1. Right click your lists and choose "Add as new query".
2. Convert your columnNames list to table and transpose the table.
3.Convert your rows list to table and rename the column as follows
4.Add custom columns as follows, For more details, please review this similar blog.
5. Remove first column in rows table.
6. Append the two queries as shown in the following screenshots.
7. Select the first row in columnNames table and click the "Use first row as headers" button, then get expected result.
.
Thanks,
Lydia Zhang
Hi aurischa，
Please follow the following steps to import your JSON file to Power BI.
1. Right click your lists and choose "Add as new query".
2. Convert your columnNames list to table and transpose the table.
3.Convert your rows list to table and rename the column as follows
4.Add custom columns as follows, For more details, please review this similar blog.
5. Remove first column in rows table.
6. Append the two queries as shown in the following screenshots.
7. Select the first row in columnNames table and click the "Use first row as headers" button, then get expected result.
.
Thanks,
Lydia Zhang
Hi Lydia,
Excellent solution, and thank you for your help. My only small qualm is that it may not be scalable should the json data set contain say 20 or 100 colums. However, before reading this I did recently come up with another way to achieve this solution.
Fortunately I was able to use ROQL syntax in the REST API call meaning I was able to format the JSON file as such.
?query=SELECT%20ID,%27֍%27,subject,%27֍%27,category,%27֍%27%20from%20incidents
Where ֍ is the delimiter.
{ "items": [ { "tableName": "Table0", "count": 20000, "columnNames": [ "id", "'֍'", "subject", "'֍'", "category", "'֍'" ], "rows": [ [ "61", "֍", "Test", "֍", null, "֍" ], [ "65", "֍", "TEST 2", "֍", null, "֍" ],Final step is to rename headings.
Now that I look at it there are a few redundant steps in there. But I achieved a similar result using delimiters programmaticly which someone might find useful.
It would be nice to be able to define the table structure of the json file for powerbi at the point of making the web calls instead of making the call importing the data and then trying to manipulate it. In PHP to achieve this I'd wrapping the data in an array of arrays and define the first element of all sub arrays as the ID field and wrtre out all the data in a for each loop. Having a UI to do this would be amazing. It just seems so many steps to convert what is already for the most part, structured data into a table.Regards,
Adam
Hi Lydia,
Excellent solution, and thank you for your help. My only small qualm is that it may not be scalable should the json data set contain say 20 or 100 colums. However, before reading this I did recently come up with another way to achieve this solution.
Fortunately I was able to use ROQL syntax in the REST API call meaning I was able to format the JSON file as such.
?query=SELECT%20ID,%27֍%27,subject,%27֍%27,category,%27֍%27%20from%20incidents
Where ֍ is the delimiter.
{ "items": [ { "tableName": "Table0", "count": 20000, "columnNames": [ "id", "'֍'", "subject", "'֍'", "category", "'֍'" ], "rows": [ [ "61", "֍", "Test", "֍", null, "֍" ], [ "65", "֍", "TEST 2", "֍", null, "֍" ],Final step is to rename headings.
Now that I look at it there are a few redundant steps in there. But I achieved a similar result using delimiters programmaticly which someone might find useful.
It would be nice to be able to define the table structure of the json file for powerbi at the point of making the web calls instead of making the call importing the data and then trying to manipulate it. In PHP to achieve this I'd wrapping the data in an array of arrays and define the first element of all sub arrays as the ID field and wrtre out all the data in a for each loop. Having a UI to do this would be amazing. It just seems so many steps to convert what is already for the most part, structured data into a table.Regards,
Adam
Like to share an easier solution here, as this seems to be quite a common scenario:
Table.Combine(List.Transform(items1[rows], each Table.FromRows({_}, items1[columnNames])))No need to expand anything from the record ("items1") here: Just reference the field "rows" (which contains a list) and transform each item of the list (which is a list as well, holding the row-values of the tables) to a table with the column headers from record-field "columnNames". This will return a list of tables which you simply combine (append).
BUT if you ever find yourself in a situation where you cannot "navigate" your solution directly like this, you should use this function:
Table.FromRecords( { YourJsonRecord } )This will do your multiple steps here in one go (don't forget the curly brackets around your record, as this function requires a list of records: Even if this list has just one item )
ImkeF : Do you know what to do if the result from calling the web service is this ?
I can't get the values to link with the dimensions
Thanks in advance
This is brilliant - thanks so much Lydia. I've been looking for an answer to this problem for about 4 days straight! :)
Thanks you for this. This was driving my crazy! :)
let Source = Folder.Files("D:\MS Project\Data\JASON-0989I\1"), #"D:\MS Project\Data\JASON-0989I\1\_2019-03-15-15-55-49-180366_201c0786-a76b-478a-a702-b4dd0c3569d4 json" = Source{[#"Folder Path"="D:\MS Project\Data\JASON-0989I\1\",Name="2019-03-15-15-55-49-180366_201c0786-a76b-478a-a702-b4dd0c3569d4.json"]}[Content], #"Imported JSON" = Json.Document(#"D:\MS Project\Data\JASON-0989I\1\_2019-03-15-15-55-49-180366_201c0786-a76b-478a-a702-b4dd0c3569d4 json"), video_analysis_data = #"Imported JSON"[video_analysis_data], data = video_analysis_data[data], #"1" = data[1], data1 = #"1"[data] in data1
Superstar! thanks!
Anonymous wrote:Hi aurischa，
Please follow the following steps to import your JSON file to Power BI.
1. Right click your lists and choose "Add as new query".
2. Convert your columnNames list to table and transpose the table.
3.Convert your rows list to table and rename the column as follows
4.Add custom columns as follows, For more details, please review this similar blog.
5. Remove first column in rows table.
6. Append the two queries as shown in the following screenshots.
7. Select the first row in columnNames table and click the "Use first row as headers" button, then get expected result.
.
Thanks,
Lydia Zhanghi Anonymous,
Thanks to you, this works for me.
But how to make a loop like a DO WHILE ? Because I will have append 50 queries...
Any solution ?
