Parse JSON response list arrays as columns instead of rows using Power BI / Power Query / M Code

Question

I am stuck trying to transform a JSON file using Power BI / Power Query / M Code.

I would like to parse my sample JSON data (provided below) to a tabular format like this:

My Power BI / Power Query / M Code so far

let
    Source = Json.Document(File.Contents("C:\demo.json")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"ReportID", "ReportName", "ReportType", "ReportTitles", "ReportDate", "UpdatedDateUTC", "Rows"}, {"ReportID", "ReportName", "ReportType", "ReportTitles", "ReportDate", "UpdatedDateUTC", "Rows"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Value1",{"Name", "ReportID", "ReportName", "ReportType", "ReportTitles", "ReportDate", "UpdatedDateUTC"}),
    #"Expanded Rows" = Table.ExpandListColumn(#"Removed Columns", "Rows"),
    #"Expanded Rows1" = Table.ExpandRecordColumn(#"Expanded Rows", "Rows", {"RowType", "Cells", "Title", "Rows"}, {"RowType", "Cells", "Title", "Rows.1"})
in
    #"Expanded Rows1"

My code gets up to point where query results has one row for header content and one row for each of the two sections' contents.

I am stuck on how to expand the JSON lists of arrays as columns instead of rows? Specifically:

List in Header row Cells column
List in the two Section rows Rows.1 column

Any tips, hints, ideas, pointers?

Sample JSON file

{
  "Reports": [
    {
      "ReportID": "ProfitAndLoss",
      "ReportName": "Profit and Loss",
      "ReportType": "ProfitAndLoss",
      "ReportTitles": [
        "Profit & Loss",
        "Demo Company (AU)",
        "1 February 2018 to 28 February 2018"
      ],
      "ReportDate": "25 February 2018",
      "UpdatedDateUTC": "\/Date(1519593468971)\/",
      "Rows": [
        {
          "RowType": "Header",
          "Cells": [
            { "Value": "" },
            { "Value": "28 Feb 18" },
            { "Value": "28 Jan 18" }
          ]
        },
        {
          "RowType": "Section",
          "Title": " Income",
          "Rows": [
            {
              "RowType": "Row",
              "Cells": [
                {
                  "Value": "Sales",
                  "Attributes": [
                    {
                      "Value": "e2bacdc6-2006-43c2-a5da-3c0e5f43b452",
                      "Id": "account"
                    }
                  ]
                },{
                  "Value": "9220.05",
                  "Attributes": [
                    {
                      "Value": "e2bacdc6-2006-43c2-a5da-3c0e5f43b452",
                      "Id": "account"
                    }
                  ]
                },{
                  "Value": "5120.05",
                  "Attributes": [
                    {
                      "Value": "e2bacdc6-2006-43c2-a5da-3c0e5f43b452",
                      "Id": "account"
                    }
                  ]
                }
              ]
            },
            {
              "RowType": "SummaryRow",
              "Cells": [
                { "Value": "Total Income" },
                { "Value": "9220.05" },
                { "Value": "1250.09" }
              ]
            }
          ]
        },{
          "RowType": "Section",
          "Rows": [
            {
              "RowType": "Row",
              "Cells": [
                { "Value": "NET PROFIT" },
                { "Value": "-6250.09" },
                { "Value": "-7250.09" }
              ]
            }
          ]
        }
      ]
    }
  ]
}

Joao Leal Joao Leal · Accepted Answer · 2021-07-26T00:01:41

There might be a better way of doing this, but this is what I came up with:

let
    Source = Json.Document(File.Contents("document.json")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"ReportID", "ReportName", "ReportType", "ReportTitles", "ReportDate", "UpdatedDateUTC", "Rows"}, {"ReportID", "ReportName", "ReportType", "ReportTitles", "ReportDate", "UpdatedDateUTC", "Rows"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Value1",{"Name", "ReportID", "ReportName", "ReportType", "ReportTitles", "ReportDate", "UpdatedDateUTC"}),
    #"Expanded Rows" = Table.ExpandListColumn(#"Removed Columns", "Rows"),
    #"Expanded Rows1" = Table.ExpandRecordColumn(#"Expanded Rows", "Rows", {"RowType", "Cells", "Title", "Rows"}, {"RowType", "Cells", "Title", "Rows.1"}),
    //Get the headers
    #"HeaderRaw" = #"Expanded Rows1"{0}[Cells],
    #"Converted to Table1" = Table.FromList(HeaderRaw, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"Value"}, {"Column1.Value"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Column1", "Index", 1, 1, Int64.Type),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Index", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","","blank",Replacer.ReplaceValue,{"Column1.Value"}),
    oldColumnNames = Table.Column(#"Replaced Value", "Index"),
    newColumnNames = Table.Column(#"Replaced Value", "Column1.Value"),
    #"RenameRecords" = List.Zip({oldColumnNames, newColumnNames}),
    //Get contents
    #"Removed Top Rows" = Table.Skip(#"Expanded Rows1",1),
    #"combine" = Table.SelectColumns(#"Removed Top Rows",{"RowType", "Title", "Rows.1"}),
    #"Expanded Rows.2" = Table.ExpandListColumn(combine, "Rows.1"),
    #"Expanded Rows.1" = Table.ExpandRecordColumn(#"Expanded Rows.2", "Rows.1", {"RowType", "Cells"}, {"Rows.1.RowType", "Rows.1.Cells"}),
    #"Expanded Rows.1.Cells" = Table.ExpandListColumn(#"Expanded Rows.1", "Rows.1.Cells"),
    #"Grouped Rows" = Table.Group(#"Expanded Rows.1.Cells", {"Title", "Rows.1.RowType"}, {{"Count", each _, type table [RowType=text, Title=nullable text, Rows.1.RowType=text, Rows.1.Cells=record]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index", 1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"RowType", "Title", "Rows.1.RowType", "Rows.1.Cells", "Index"}, {"Custom.RowType", "Custom.Title", "Custom.Rows.1.RowType", "Custom.Rows.1.Cells", "Custom.Index"}),
    #"Expanded Custom.Rows.1.Cells" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom.Rows.1.Cells", {"Value", "Attributes"}, {"Custom.Rows.1.Cells.Value", "Custom.Rows.1.Cells.Attributes"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom.Rows.1.Cells",{"Custom.Rows.1.Cells.Attributes"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.Index", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.Index", type text}}, "en-GB")[Custom.Index]), "Custom.Index", "Custom.Rows.1.Cells.Value"),
    #"Final" = Table.RenameColumns(#"Pivoted Column", #"RenameRecords")
in
    #"Final"

Get the header record and values, add an index and convert that into "old column name" and "new column name" (you will get 1 - blank, 2 - 28 Feb, 3 - 28 Jan)
Get the remainder of records and expand them all, then group by the type and add an index, so you will get something like: Total Income - 1, 9220.5 - 2, 1250.5 - 3
Then Pivot the table on the index column, using the value of the cells, which will get you the values into columns and the columns will be called 1,2 and 3
Finally rename the columns based on what we did on step 1

Parse JSON response list arrays as columns instead of rows using Power BI / Power Query / M Code

1 Answers