Forum Discussion

anntom2023's avatar
anntom2023
New Member
3 years ago

Parsing JSON nested list and individual records in Power Query

Hi PBI Guru’s,   In the JSON files, rows have a nested list and lower-level individual records as seen below.   "rows": [     {       "dimensionValues": [         {           "value": "202307...
Need Help
lbendlin's avatar
lbendlin
Super User
3 years ago

Yeah, this is not going to be simple or fast... May want to throw in a Binary.Buffer().

 

 

let
    Source = Json.Document(File.Contents("C:\Users\xxx\Downloads\rows.json"))[rows],
    #"Converted to Table1" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table1", "Custom", each [Column1][dimensionValues] & [Column1][metricValues]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.ExpandRecordColumn(Table.FromList([Custom], Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", {"value"}, {"value"})[value]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom.1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Custom.1.1", "Custom.1.2", "Custom.1.3", "Custom.1.4", "Custom.1.5", "Custom.1.6", "Custom.1.7"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Column1", "Custom"})
in
    #"Removed Columns"

 

 

it feels like there could be some improvements in some of the steps but I haven't found the right technique yet.

 

I added more rows to make your sample data usable.

lbendlin's avatar
lbendlin
Super User
3 years ago

Here's a slightly more concise version using List.Transform:

 

 

let
    Source = Json.Document(File.Contents("C:\Users\xxx\Downloads\rows.json"))[rows],
    #"Combined Lists" = List.Transform(Source,each _[dimensionValues] & _[metricValues]),
    #"Converted to Table" = Table.FromList(#"Combined Lists", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each List.Transform([Column1],each _[value])),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Column1"})
in
    #"Removed Columns"