cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft
Microsoft

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
v-yuezhe-msft
Microsoft
Microsoft

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.
Anonymous
Not applicable

hi @v-yuezhe-msft,

 

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 ?

Superstar! thanks!


@v-yuezhe-msft 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".
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


 

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

Thanks you for this. This was driving my crazy!  🙂

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

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,
                    "֍"
                ],

1 nav.png

 

 

2 convert to table.png

 

 

3 Expand colmun.png

 

 

4 Expand column1 - n.png

 

 

5 removed columns.png

 

 

6 Transpose table.png

 

 

7 promote headers.png

 

 

8 Expand subject.png

 

 

9 extract values.png

 

 

10 removed columns1.png

 

 

11 split colums.png

 

 

12 changed type.png

 

 

 

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 ) 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Yggdrasill
Responsive Resident
Responsive Resident

@ImkeF : Do you know what to do if the result from calling the web service is this ?

eurostatres.PNG

 

 

I can't get the values to link with the dimensions

 

Thanks in advance

Did you try Table.FromRecords( { MyJsonRecord } ) like I've described in the post above already? (or here: http://www.thebiccountant.com/2017/08/30/how-to-open-a-complex-json-record-in-power-bi-and-power-que... )

 

If so: How do you want the records to be linked exactly?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi,

 

I'm curious to get your feedback on this json, I tried you use your method and it did not yield the results I needed.  I was able to accomplish the multi-layered expansion (with some help from a friend).

 

Here is my JSON

{"message":"accurate","cod":"200","count":10,"list":[{"id":495260,"name":"Shcherbinka","coord":{"lat":55.4997,"lon":37.5597},"main":{"temp":269.91,"pressure":1020,"humidity":85,"temp_min":269.15,"temp_max":270.15},"dt":1549571400,"wind":{"speed":2,"deg":190},"sys":{"country":""},"rain":null,"snow":null,"clouds":{"all":0},"weather":[{"id":800,"main":"Clear","description":"Sky is Clear","icon":"01n"}]},{"id":564517,"name":"Dubrovitsy","coord":{"lat":55.4397,"lon":37.4867},"main":{"temp":269.91,"pressure":1020,"humidity":85,"temp_min":269.15,"temp_max":270.15},"dt":1549571400,"wind":{"speed":2,"deg":190},"sys":{"country":""},"rain":null,"snow":null,"clouds":{"all":0},"weather":[{"id":800,"main":"Clear","description":"Sky is Clear","icon":"01n"}]},{"id":570578,"name":"Butovo","coord":{"lat":55.5483,"lon":37.5797},"main":{"temp":269.91,"pressure":1020,"humidity":85,"temp_min":269.15,"temp_max":270.15},"dt":1549571400,"wind":{"speed":2,"deg":190},"sys":{"country":""},"rain":null,"snow":null,"clouds":{"all":0},"weather":[{"id":800,"main":"Clear","description":"Sky is Clear","icon":"01n"}]},{"id":545782,"name":"Kommunarka","coord":{"lat":55.5695,"lon":37.4893},"main":{"temp":269.91,"pressure":1020,"humidity":85,"temp_min":269.15,"temp_max":270.15},"dt":1549571400,"wind":{"speed":2,"deg":190},"sys":{"country":""},"rain":null,"snow":null,"clouds":{"all":0},"weather":[{"id":800,"main":"Clear","description":"Sky is Clear","icon":"01n"}]},{"id":6417490,"name":"Lesparkkhoz","coord":{"lat":55.5431,"lon":37.6014},"main":{"temp":269.91,"pressure":1020,"humidity":85,"temp_min":269.15,"temp_max":270.15},"dt":1549571400,"wind":{"speed":2,"deg":190},"sys":{"country":""},"rain":null,"snow":null,"clouds":{"all":0},"weather":[{"id":800,"main":"Clear","description":"Sky is Clear","icon":"01n"}]},{"id":526736,"name":"Sed’moy Mikrorayon","coord":{"lat":55.5622,"lon":37.5797},"main":{"temp":269.91,"pressure":1020,"humidity":85,"temp_min":269.15,"temp_max":270.15},"dt":1549571400,"wind":{"speed":2,"deg":190},"sys":{"country":""},"rain":null,"snow":null,"clouds":{"all":0},"weather":[{"id":800,"main":"Clear","description":"Sky is Clear","icon":"01n"}]},{"id":473051,"name":"Vlas’yevo","coord":{"lat":55.4603,"lon":37.3794},"main":{"temp":269.92,"pressure":1020,"humidity":85,"temp_min":269.15,"temp_max":270.15},"dt":1549571400,"wind":{"speed":2,"deg":190},"sys":{"country":""},"rain":null,"snow":null,"clouds":{"all":0},"weather":[{"id":800,"main":"Clear","description":"Sky is Clear","icon":"01n"}]},{"id":578680,"name":"Bachurino","coord":{"lat":55.58,"lon":37.52},"main":{"temp":269.91,"pressure":1020,"humidity":85,"temp_min":269.15,"temp_max":270.15},"dt":1549571400,"wind":{"speed":2,"deg":190},"sys":{"country":""},"rain":null,"snow":null,"clouds":{"all":0},"weather":[{"id":800,"main":"Clear","description":"Sky is Clear","icon":"01n"}]},{"id":554629,"name":"Shestoy Mikrorayon","coord":{"lat":55.5667,"lon":37.5833},"main":{"temp":269.91,"pressure":1020,"humidity":85,"temp_min":269.15,"temp_max":270.15},"dt":1549571400,"wind":{"speed":2,"deg":190},"sys":{"country":""},"rain":null,"snow":null,"clouds":{"all":0},"weather":[{"id":800,"main":"Clear","description":"Sky is Clear","icon":"01n"}]},{"id":508101,"name":"Podolsk","coord":{"lat":55.4242,"lon":37.5547},"main":{"temp":269.91,"pressure":1020,"humidity":79,"temp_min":269.15,"temp_max":270.15},"dt":1549571400,"wind":{"speed":2,"deg":220},"sys":{"country":""},"rain":null,"snow":null,"clouds":{"all":0},"weather":[{"id":800,"main":"Clear","description":"Sky is Clear","icon":"01n"}]}]}

   

The code to expand all of this was this

let
    Source = Json.Document(Web.Contents("http://api.openweathermap.org/data/2.5/find?lat=55.5&lon=37.5&cnt=10&APPID=xxxxxxxxxxxxxxxxx")),
    RecordList = Source[list],
    RecordTable = Table.FromList(RecordList, Record.FieldValues),
    #"Expanded Column3" = Table.ExpandRecordColumn(RecordTable, "Column3", {"lat", "lon"}, {"lat", "lon"}),
    #"Expanded Column4" = Table.ExpandRecordColumn(#"Expanded Column3", "Column4", {"temp", "pressure", "humidity", "temp_min", "temp_max"}, {"temp", "pressure", "humidity", "temp_min", "temp_max"}),
    #"Expanded Column6" = Table.ExpandRecordColumn(#"Expanded Column4", "Column6", {"speed", "deg"}, {"speed", "deg"}),
    #"Expanded Column7" = Table.ExpandRecordColumn(#"Expanded Column6", "Column7", {"country"}, {"country"}),
    #"Expanded Column10" = Table.ExpandRecordColumn(#"Expanded Column7", "Column10", {"all"}, {"all"}),
    #"Expanded Column11" = Table.ExpandListColumn(#"Expanded Column10", "Column11"),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Expanded Column11", "Column11", {"id", "main", "description", "icon"}, {"id", "main", "description", "icon"})
in
    #"Expanded Column1"

But the real key to it all is the top few lines and the bottom line to expand all the nested json data.

 

I used the weather site to pull some free json data to test out my theory. 

 

Anonymous
Not applicable

Thanks for taking your time looking into this. 

 

What I'm trying to do is to get some sense out of JSON-stat file from Eurostat (See here for the JSON-stat file and here for the table it self )

I've worked with JSON before and done some tricks here and there to create tables but this time I'm just stuck.

 

This is what I get from get data - Web - using this url: 

http://ec.europa.eu/eurostat/wdds/rest/data/v2.1/json/en/tin00171?nace_r2=I551-I553&precision=1&last...

Capture1.PNG

 

 

 

 

 

 

 

 

 

 

 

After using the Table.FromRecords function and removing some columns I don't need I get this

 

Capture2.PNG

Result:

Capture3.PNG

 

when I click on the "cells" get this from the lower pane view 

 

behind dimension recordsbehind dimension recordsbehind value recordsbehind value records

 

After going back and forth trying to attach the values to dimension I simply can't and that is possibly because of my lack of M-formula knowledge.

 

Is there a way I can get a table with the fields geo and time with correct values ? 

 

Kind regards and again, thanks for taking your time to look into this

This is in JSON-stat format.

If you can use R-script in PowerBI there is a simple example at Statistics Norway's API pages using the r-packages httr and rjstat. https://www.ssb.no/en/omssb/tjenester-og-verktoy/api/px-api/code-examples

 

 

So you want to link the "unit"-record to the number 2504985?

How? As a new record? Which field name shall the number get?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

 

Capture6.PNGI basically just want the output to be tabular - like this 


 

 

 

 

 

 

 

I couldn't spot a key or pattern between these 2 items.

So unfortunately I have no idea how to match them, sorry.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

Oh well, thanks anyway : )

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,
                    "֍"
                ],

1 nav.png

 

 

2 convert to table.png

 

 

3 Expand colmun.png

 

 

4 Expand column1 - n.png

 

 

5 removed columns.png

 

 

6 Transpose table.png

 

 

7 promote headers.png

 

 

8 Expand subject.png

 

 

9 extract values.png

 

 

10 removed columns1.png

 

 

11 split colums.png

 

 

12 changed type.png

 

 

 

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

 

 

 

 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors