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"
                },
                "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?
 
 

 

 

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.

     

     

3 Replies

  • ImkeF's avatar
    ImkeF
    Community 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

       

       

       

       

       

  • ImkeF's avatar
    ImkeF
    Community Champion
    4 years ago

    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.