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

     

     

steve1024's avatar
steve1024
Frequent Visitor
3 years ago

just for clarity: the desired result should look like this (three seperat columns, 2 key columnd and one value column)

 

KEY 1   KEY 2    Value
KIFI2022M10    123.3