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
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 )
ImkeF : Do you know what to do if the result from calling the web service is this ?
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-query/ )
If so: How do you want the records to be linked exactly?
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:
After using the Table.FromRecords function and removing some columns I don't need I get this
Result:
when I click on the "cells" get this from the lower pane view
behind dimension 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
So you want to link the "unit"-record to the number 2504985?
How? As a new record? Which field name shall the number get?
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.
