How to get a row per nested field

[Anonymous]

{

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

                }

            }

        }

    }

}

 

How do I import this JSON into excel to a table with a row per load id and columns record id, file_no, load_id, docket number?

 

 

[ImkeF]

Hello @Anonymous ,
have read your request 2 times, but still don't understand it, I'm sorry.
Please create an Excel file with some sample data (data sources and desired result).
Also, please create a new thread for this question and post the link to it here.
Thanks.

[ImkeF · Accepted solution]

Hi @Anonymous ,
the function Record.ToTable is your friend for this task:

let
    Source = "{#(lf)    ""records"": {#(lf)        ""record_id_1"": {#(lf)            ""file_no"": ""5792C"",#(lf)            ""loads"": {#(lf)                ""load_id_1"": {#(lf)                    ""docket_no"": ""3116115""#(lf)                },#(lf)                ""load_id_2"": {#(lf)                    ""docket_no"": ""3116118""#(lf)                },#(lf)                ""load_id_3"": {#(lf)                    ""docket_no"": ""3208776""#(lf)                }#(lf)            }#(lf)        },#(lf)        ""record_id_2"": {#(lf)            ""file_no"": ""5645C"",#(lf)            ""loads"": {#(lf)                ""load_id_4"": {#(lf)                    ""docket_no"": ""2000527155""#(lf)                },#(lf)                ""load_id_5"": {#(lf)                    ""docket_no"": ""2000527156""#(lf)                },#(lf)                ""load_id_6"": {#(lf)                    ""docket_no"": ""2000527146""#(lf)                }#(lf)            }#(lf)        }#(lf)    }#(lf)}",
    #"Parsed JSON" = Json.Document(Source),
    records = #"Parsed JSON"[records],
    #"Converted to Table" = Record.ToTable(records),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"file_no", "loads"}, {"file_no", "loads"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Value", "Custom", each Record.ToTable([loads])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"loads"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Name", "Value"}, {"Name.1", "Value"}),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Custom", "Value", {"docket_no"}, {"docket_no"})
in
    #"Expanded Value1"

Paste the code above into the advanced editor of a blank query and follow the steps.

Step "Added Custom" contains the action that enables the expansion to one load id per row.

[Anonymous]

Hi ImkeF,

I have a similar but slighlty different problem. I'm looking for a better way to merge 2 nested tables

I'm after this result

I have a column [list from row] which contains a list from [Column1]

I've also made this into a nested table (if that helps)

I have a separate nested table in [levytable] that I want to lookup and return the corresponding 'Levy $' value.

I can find the position of one of the items if it is a singluar item listed in a column

= Table.AddColumn(#"Added Custom2", "List Position Test", each List.PositionOf([levytable][#"Wording in Regs"],[column for List Postion test]))

What I would like to get is this an added  column to the nested table that contains the matching amount. For example the first row in [Column1] in the nested table would have "Secondary Processors - Domestic and the [Levy $] column would contain "710.1"

  

I can get this with a nested join but it seems to slow the whole process as there is a lot of data

Any help would be appreciated.

Thanks

Leroy