Multi-Dimensional Json file to Table

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

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.