Forum Discussion
Multi-Dimensional Json file to Table
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".
2. Convert your columnNames list to table and transpose the table.
3.Convert your rows list to table and rename the column as follows
4.Add custom columns as follows, For more details, please review this similar blog.
5. Remove first column in rows table.
6. Append the two queries as shown in the following screenshots.
7. Select the first row in columnNames table and click the "Use first row as headers" button, then get expected result.
.
Thanks,
Lydia Zhang
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-query/ )
If so: How do you want the records to be linked exactly?
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.