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! 🙂

jdogcisco Frequent Visitor
Frequent Visitor

Re: Multi-Dimensional Json file to Table

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

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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Top Kudoed Authors
Users Online
Currently online: 26 members 1,883 guests
Please welcome our newest community members: