Apr 25 2018
10:10 AM
- last edited on
Jul 12 2019
11:07 AM
by
TechCommunityAP
Apr 25 2018
10:10 AM
- last edited on
Jul 12 2019
11:07 AM
by
TechCommunityAP
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}, {n3} ],
name2: [ [v1,v2,v3], [v1,v2,v3], ... ] ] }
The Excel data import seems to just get the two top level names ("name1", "name2"), and none of the data.
Sample data:
{ "fields": [
{ "type": "datetime",
"name": "Observation Valid",
"description": "Observation Valid Time"
},
{ "type": "datetime",
"name": "Observation Valid UTC",
"description": "Observation Valid Time UTC"
}
],
"rows": [
[ "2018-04-22T00:10:00",
"2018-04-22T05:10:00Z"
],
[ "2018-04-22T00:15:00",
"2018-04-22T05:15:00Z"
],
[ "2018-04-22T00:20:00",
"2018-04-22T05:20:00Z"
]
]
}
Apr 26 2018 12:02 PM
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
Apr 26 2018 03:40 PM
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?
Apr 26 2018 05:45 PM - edited Apr 26 2018 05:46 PM
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.
Apr 27 2018 11:31 AM
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.
Apr 28 2018 10:13 AM
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.
Apr 28 2018 11:24 AM
SolutionHi 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
Aug 29 2018 07:51 AM
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?
Aug 29 2018 09:41 AM
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.
Aug 31 2018 11:17 AM
Thank you Sergei, I managed to do exactly what I wanted.
Apr 28 2018 11:24 AM
SolutionHi 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