Parsing JSON nested list and individual records in Power Query

[anntom2023]

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

[lbendlin]

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]

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"

[foodd]

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-...

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.