CODEWITHSUNDEEP
jsonpowerbipowerquery
curtisp
curtisp

Reputation: 2315

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

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:

enter image description here

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.

enter image description here

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

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" }
              ]
            }
          ]
        }
      ]
    }
  ]
}

Upvotes: 0

Views: 1800

Answers (1)

Joao Leal
Joao Leal

Reputation: 5542

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"

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

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

  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

  4. Finally rename the columns based on what we did on step 1

Upvotes: 1

Related Questions