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
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.
Very nice - thanks. I now see where this code goes - "Advanced Query Editor".
The URL of the data source specifies the data site, and embedded in it is a field which says which day of data to access. I would like to have a cell in the spreadsheet, which defines the date of interest, and have the query URL link to that. Or perhaps, form the new URL in a cell, and just have the URL link to that cell?
Or, some VBA which wold change the URL of the data source?
The basic idea is - change the date cell, and automatically by some refresh button, the data and graph changes.
Many thanks.