parse two-level JSON data

[Gregory Guthrie]

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"
    ]
  ]
}

[#2]

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

[#3]

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?

[#4]

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.

[#5]

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.

[#6]

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.

[#7 · Accepted solution]

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

[#8]

Ah, very nice - thank you, I had missed that!!

[#9]

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?

[#10]

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.

[#11]

Thank you Sergei, I managed to do exactly what I wanted.