Forum Discussion

Gregory Guthrie's avatar
Gregory Guthrie
Copper Contributor
Apr 25, 2018

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

BI & Data Analytics
import data from web
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 28, 2018

    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

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Apr 26, 2018

    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

    • Gregory Guthrie's avatar
      Gregory Guthrie
      Copper Contributor
      Apr 26, 2018

      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?

      • Gregory Guthrie's avatar
        Gregory Guthrie
        Copper Contributor
        Apr 27, 2018

        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.

    • Éric Brien's avatar
      Éric Brien
      Copper Contributor
      Aug 29, 2018

      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?

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Aug 29, 2018

        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.

Resources