Search code examples
excelpowerquerym

How to specify array element based on a value in Power Query for JSON

I have a JSON source of nested arrays. Each object has a "Key" element. I want to select the element where Key="canada". How would I specify this?

What works:

let
    Source = Json.Document(Web.Contents("https://kustom.radio-canada.ca/covid-19")),
    Source1 = Source{22},

But it's hardcoded as the 22nd value.

What doesn't work:

    Source1 = Source{[Key="canada"]}

This returns:

Expression.Error: We cannot convert a value of type Record to type Number.
Details:
    Value=
        Key=canada
    Type=[Type]

So, how would I specify this?

Solution

  • Try this

    let
    Source = Json.Document(Web.Contents("https://kustom.radio-canada.ca/covid-19")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Key", "Api", "Name", "NameFr", "Country", "CountryFr", "State", "StateFr", "Lat", "Long", "Confirmed", "Deaths", "Recovered", "Population", "History", "Regions", "DateUpdate"}, {"Key", "Api", "Name", "NameFr", "Country", "CountryFr", "State", "StateFr", "Lat", "Long", "Confirmed", "Deaths", "Recovered", "Population", "History", "Regions", "DateUpdate"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each ([Key] = "canada"))
in
    #"Filtered Rows"