Forum Discussion

James_SR's avatar
James_SR
New Member
7 years ago
Solved

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

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? 

Need Help

4 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    7 years ago

    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.