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.