Forum Discussion
Solved
parse two-level JSON data
I need to import some weather data (https://mesonet.agron.iastate.edu/json/obhistory.py?station=TVK&network=AWOS&date=2018-04-22), which has a two-level JSON structure - i.e. { name1: [ {n1}, {n2...
Hi Gregory,
If you check the file I sent second time the date is in cell A1 and named as ObservationDate
Power Query picks it up and embeds into the URL
You may change the date in this cell and after that Data->Refresh All
Hi Gregory,
You need to add some transformations in Power Query, as variant
let
Source = Json.Document(Web.Contents("https://mesonet.agron.iastate.edu/json/obhistory.py?station=TVK&network=AWOS&date=2018-04-22")),
SourceToTable = Record.ToTable(Source),
FilterFields = Table.SelectRows(SourceToTable, each ([Name] = "fields")),
ExpandListWithRecords = Table.ExpandListColumn(FilterFields, "Value"),
TakeNames = Table.AddColumn(ExpandListWithRecords, "Custom", each [Value][name]),
RemoveUnused = Table.SelectColumns(TakeNames,{"Custom"}),
ListOfHeaders = Table.ToList(RemoveUnused),
FilterRows = Table.SelectRows(SourceToTable, each ([Name] = "rows")),
ExpandRows = Table.ExpandListColumn(FilterRows, "Value"),
ExtractLists = Table.TransformColumns(ExpandRows, {"Value", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
SplitOnColumns = Table.SplitColumn(ExtractLists, "Value", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), ListOfHeaders),
RemoveNames = Table.RemoveColumns(SplitOnColumns,{"Name"})
in
RemoveNames
and attached
Hi, this was useful. Though I was able to apply this on a single json file, how can I do the same thing but using a folder with mulitple json files with same type of data?
Eric, just a raw idea - what you did for the single file transform to the function with full file path as parameter, e.g. fnTrasformJSON
Connect to the folder with your json files, you'll have the table with filenames and other attributes. Add to it custom column with =fnTransformJSON([filename]). The Table shall be result value.
Remove all other columns and expand this one.
Thank you Sergei, I managed to do exactly what I wanted.
Thanks! It's beautiful and just want I want, but could you outline the steps?
My goal is a sheet that then has a graph of this, so I can click an "update data" button, and see the graph for a particular day. That would also mean, changing the text in the URL, since it includes the day of interest as a parameter - can that be a cell reference?
I also notice, that a returned field of
[ "2018-04-22T00:10:00",
"2018-04-22T05:10:00Z",
50,
9,
null,
50
]the last field (direction) gets put instead into the "Gust" column.
Hi Gregory,
You may define the date as parameter on Power Query or define it within Excel sheet in any cell to give it the name. Or you may generate a list with dates and pick-up the date from it into such cell. In attached file that is A1 named as ObservationDate.
Fields with null are corrected.
Not sure how familiar you are with PowerQuery. I added few comments to the script which you may see in Advanced Editor, to understand how it works better to go step by step in Quick Editor.
Please check attached file. If any question please ask what exactly is unclear or doesn't work as expected.
