Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to get a row per nested field

{     "records": {         "record_id_1": {             "file_no": "5792C",             "loads": {                 "load_id_1": {                     "docket_no": "3116115"                 },       ...
General Comment
  • ImkeF's avatar
    ImkeF
    4 years ago

    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.

     

     

ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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