Forum Discussion

steve1024's avatar
steve1024
Frequent Visitor
3 years ago
Solved

Expand list and column

Hi,   I have a table with two columns (key = list of with two key values in each item & values = a numeric measure)     each item of the list looks like this:   I would like to tra...
Need Help
  • AntrikshSharma's avatar
    AntrikshSharma
    3 years ago

    steve1024 Use Record.FromList (), paste the below code in Advanced Editor:

    let
    Source = 
        Table.FromRows (
            { { { "KIFI", "1996M11" }, 91 } } & { { { "KIKI", "12346" }, 125 } },
            type table [ Key = list, Values = number ]
        ),
    AddedCustom = 
        Table.AddColumn (
            Source,
            "Custom",
            each Record.FromList ( [Key], { "Key 1", "Key 2" } )
        ),
    ExpandedCustom = 
        Table.ExpandRecordColumn (
            AddedCustom,
            "Custom",
            { "Key 1", "Key 2" },
            { "Key 1", "Key 2" }
        ),
    RemovedColumns = Table.RemoveColumns ( ExpandedCustom, { "Key" } )
in
    RemovedColumns

     

     

AntrikshSharma's avatar
AntrikshSharma
Community Champion
3 years ago

steve1024 Use Record.FromList (), paste the below code in Advanced Editor:

let
    Source = 
        Table.FromRows (
            { { { "KIFI", "1996M11" }, 91 } } & { { { "KIKI", "12346" }, 125 } },
            type table [ Key = list, Values = number ]
        ),
    AddedCustom = 
        Table.AddColumn (
            Source,
            "Custom",
            each Record.FromList ( [Key], { "Key 1", "Key 2" } )
        ),
    ExpandedCustom = 
        Table.ExpandRecordColumn (
            AddedCustom,
            "Custom",
            { "Key 1", "Key 2" },
            { "Key 1", "Key 2" }
        ),
    RemovedColumns = Table.RemoveColumns ( ExpandedCustom, { "Key" } )
in
    RemovedColumns

 

 

leymandudu's avatar
leymandudu
New Member
3 years ago

 

 

Hello, thanks for your response to the question. I have a similar issue but mine is a little complicated. I am using a JSON for my analysis containing lots of arrays and embedded documents. I have been able to expand all the arrays and embedded documents but I want them to be in a single row.

I look forward to hearing from you.

 

Thanks and regards.