JSON to Excel PowerQuery import - how to get a row per nested field-CodePudding

I'm looking to use the Excel Power Query to import some json that looks like the following (but much bigger, more fields etc.):

example-records.json

{
    "records": {
        "record_id_1": {
            "file_no": "5792C",
            "loads": {
                "load_id_1": {
                    "docket_no": "3116115"
                },
                "load_id_2": {
                    "docket_no": "3116118"
                },
                "load_id_3": {
                    "docket_no": "3208776"
                }
            }
        },
        "record_id_2": {
            "file_no": "5645C",
            "loads": {
                "load_id_4": {
                    "docket_no": "2000527155"
                },
                "load_id_5": {
                    "docket_no": "2000527156"
                },
                "load_id_6": {
                    "docket_no": "2000527146"
                }
            }
        }
    }
}

I want to get a table like the following at the load_id / docket level. A row per load_id

What I've tried

Clicking buttons in power query UI I get the following.

The problem is I can't include a file_no column and this doesn't work when there are lots of load ids.

let
    Source = Json.Document(File.Contents("H:\Software\Site Apps\example-records.json")),
    records = Source[records],
    #"Converted to Table" = Record.ToTable(records),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"file_no", "loads"}, {"Value.file_no", "Value.loads"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Value",{"Value.file_no"}),
    #"Expanded Value.loads" = Table.ExpandRecordColumn(#"Removed Columns", "Value.loads", {"load_id_1", "load_id_2", "load_id_3", "load_id_4", "load_id_5", "load_id_6"}, {"Value.loads.load_id_1", "Value.loads.load_id_2", "Value.loads.load_id_3", "Value.loads.load_id_4", "Value.loads.load_id_5", "Value.loads.load_id_6"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded Value.loads", {"Name"}, "Attribute", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Unpivoted Columns", "Value", {"docket_no"}, {"Value.docket_no"})
in
    #"Expanded Value1"

CodePudding user response：

You can use

let Source = JSON(Json.Document(File.Contents("c:\temp\example.json"))),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Name.1", "Name.3", "Value"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each if [Name.3]=null then [Value] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Name.3] <> null))
in  #"Filtered Rows"

based on this function I named JSON which comes from Imke

CodePudding user response：

Managed to use an added custom column, the action that enables the expansion to one load id per row.

    #"Added Custom" = Table.AddColumn(#"Expanded Value", "Custom", each Record.ToTable([Value.loads]))

let
    Source = Json.Document(File.Contents("H:\Software\Site Apps\example-records.json")),
    records = Source[records],
    #"Converted to Table" = Record.ToTable(records),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"file_no", "loads"}, {"Value.file_no", "Value.loads"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Value", "Custom", each Record.ToTable([Value.loads])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value.loads"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Name", "Value"}, {"Custom.Name", "Custom.Value"}),
    #"Expanded Custom.Value" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom.Value", {"docket_no"}, {"Custom.Value.docket_no"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom.Value",{{"Name", "record_id"}, {"Value.file_no", "file_no"}, {"Custom.Name", "load_id"}, {"Custom.Value.docket_no", "docket_no"}})
in
    #"Renamed Columns"