Forum Discussion
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...
you can find some further concepts on how to handle JSON in Power Query here: https://www.thebiccountant.com/tag/json/
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.
you can find some further concepts on how to handle JSON in Power Query here: https://www.thebiccountant.com/tag/json/
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?
The answer is in this post:
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 MyJsonTableThank you to ImkeF for the answer
