Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
aurischa
Regular Visitor

Multi-Dimensional Json file to Table

I have a Json file formatted like so:

{
    "items": [
        {
            "tableName": "incidents",
            "count": 20000,
            "columnNames": [
                "id",
                "subject",
                "category"
            ],
            "rows": [
                [
                    "61",
                    "Test",
                    null
                ],
                [
                    "65",
                    "TEST 2",
                    null
                ], ........Etc

I pull this Json file using a web call to an API (specifically Oracle Right Now).

 

*Note that the column names are listed as a separate entitie to the rows.

 

When I follow the suggestions on here to expand out the data I am unable to get the column headings and rows in a single table. start.pngI can navigate to the column names list OR the rows list. I can transpose the column names list to table headings like so:

  Transpose table.png

 

 But then there are no rows to fill in the columns as the rows are in a higher \ adjacent level. 

 

Navigating to the rows list simply gives me this:Rows.png

 

 

 

How do I manipulate this Json file using Power Bi to display a single table with the columns as the columns and the rows as the rows?   

 

I have spent probably too much time on this and as I'm new to M query language playing around in advanced editor has proven not very fruitful. 

 

Any help will be much appreciated. 

 

 

 

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

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".
1.PNG

2.PNG

2. Convert your columnNames list to table and transpose the table.
3.PNG4.PNG

3.Convert your rows list to table and rename the column as follows
6.PNG

4.Add custom columns as follows, For more details, please review this similar blog.

7.PNG8.PNG9.PNG

5. Remove first column in rows table.
10.PNG

6. Append the two queries as shown in the following screenshots.
11.PNG

7. Select the first row in columnNames table and click the "Use first row as headers" button, then get expected result.
12.PNG13.PNG.


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

18 REPLIES 18

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors