Forum Discussion

aurischa's avatar
aurischa
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's avatar
    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

Anonymous's avatar
Anonymous
Not applicable
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

alexadams78's avatar
alexadams78
Frequent Visitor
8 years ago

This is brilliant - thanks so much Lydia. I've been looking for an answer to this problem for about 4 days straight! :)