Error with JSON - Error with Record.List - Cannot convert the value to type List

[James_SR]

Hi all,

I'm reasonably new to Power BI so this might be an easy one.

I'm trying to extract some nested JSON data.  The information is contained, currently, as a long list of values, three for each item - area 1 has total population, female population then male population.  Then we move in to area 2 - total, female, male and so on.  It looks when expanded similar to this - this is the data for first area:

Area / values.values
Record / 5033
Record / 2628
Record / 2405

Or view this image.

The Dax I have so far is as follows:

let
    Source = Json.Document(Web.Contents("http://webservices.esd.org.uk/data?value.valueType=raw&metricType=XXX")),
    rows = Source[rows],
    #"Converted to Table" = Table.FromList(rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"area", "values"}, {"area", "values"}),
    Expand= Table.ExpandListColumn(#"Expanded Column1", "values"),
    // The JSON array is expanded here with Record.FromList    
    Custom = Table.AddColumn(Expand, "Custom", each Record.FromList([values][value], type [Total=number, Males=number, Females=number]))
in
    Custom

Up until the addition of the custom column in the last couple of rows, it works fine.  However, the Custom column with the Record.FromList fails with the error message:

Expression.Error: We cannot convert the value 2628 to type List.
Details:
    Value=2628
    Type=Type

A copy of the data is available.  Having searched online, this website shows a similar nested structure and uses Record.List.  I've also explored the Power BI community forum, including these two about nested JSON, but these don't appear to address the same issue.

Can anyone shed any light on this please? 

[ImkeF]

Hi @James_SR ,

I'd parse out your sample data like so:

let
    Source = Json.Document(Web.Contents("https://raw.githubusercontent.com/James-SR/Files/master/nesteddata.json")),
    rows = Source[rows],
    #"Converted to Table" = Table.FromList(rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"area", "values"}, {"area", "values"}),
    #"Expanded area" = Table.ExpandRecordColumn(#"Expanded Column1", "area", {"identifier", "label", "altLabel", "isSummary"}, {"identifier", "label", "altLabel", "isSummary"}),
    #"Expanded values" = Table.ExpandListColumn(#"Expanded area", "values"),
    #"Expanded values1" = Table.ExpandRecordColumn(#"Expanded values", "values", {"source", "value", "formatted", "format", "publicationStatus"}, {"source", "value", "formatted", "format", "publicationStatus"})
in
    #"Expanded values1"

Hope it hold the structure you need for your actual data. Otherwise please post sample data that matches your structure.

[ImkeF · Accepted solution]

you can find some further concepts on how to handle JSON in Power Query here: https://www.thebiccountant.com/tag/json/

[James_SR]

Thanks @ImkeF - that is a step closer.

I presume then the best next step will be to create an index, then pivot on this index?

[James_SR]

The answer is in this post:

https://www.thebiccountant.com/2017/08/30/how-to-open-a-complex-json-record-in-power-bi-and-power-qu...

Using the code:

Table.FromRecords( { MyJsonRecord } )

For example:

let
    MyJsonRecord = Json.Document(Web.Contents("http://api.tvmaze.com/singlesearch/shows?q=house-of-cards&embed=episodes")),
    MyJsonTable= Table.FromRecords( { MyJsonRecord } )
in
    MyJsonTable

Thank you to @ImkeF for the answer