Forum Discussion
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": "20230707"
},
{
"value": "Medium"
},
{
"value": "desktop"
},
{
"value": "Male"
},
{
"value": "source"
}
],
"metricValues": [
{
"value": "15"
},
{
"value": "110"
}
]
Here is how it looks in PBI query after rows are filtered & expand –
DimensionValues - Individual records
MetricValues - Individual records
Is there a way to get row values of the 5 dimensions and 2 metrics in the same step or fewer steps so that the result will look like this..
20230707 | Medium | desktop | Male | source | 15 | 110 |
I have tried functions Table.FromRows(List.Split…) but performance is terrible especially when we have multiple large JSON data files. Any help or recommendations will be much appreciated.
Thanks in advance,
Ann Tom
3 Replies
Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.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.
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"
