cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: Multi-Dimensional Json file to Table

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

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Re: Multi-Dimensional Json file to Table

Oh well, thanks anyway : )

alexadams78 Frequent Visitor
Frequent Visitor

Re: Multi-Dimensional Json file to Table

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

jdogcisco Frequent Visitor
Frequent Visitor

Re: Multi-Dimensional Json file to Table

Thanks you for this. This was driving my crazy!  Smiley Happy

janbrus Occasional Visitor
Occasional Visitor

Re: Multi-Dimensional Json file to Table

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

 

 

Re: Multi-Dimensional Json file to Table

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. 

 

anilmalviya New Member
New Member

Re: Multi-Dimensional Json file to Table

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

simonmcgee2019 Occasional Visitor
Occasional Visitor

Re: Multi-Dimensional Json file to Table

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


 

Power_BI_XploR Regular Visitor
Regular Visitor

Re: Multi-Dimensional Json file to Table

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 ?

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 492 members 3,848 guests
Please welcome our newest community members: