Forum Discussion

Regular Visitor

9 years ago

Solved

Multi-Dimensional Json file to Table

I have a Json file formatted like so: { "items": [ { "tableName": "incidents", "count": 20000, "columnNames": [ "id", ...

Need Help

Tutorial Requests

Anonymous
9 years ago
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

ImkeF

Community Champion

9 years ago

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 )