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