Expand list and column

[steve1024]

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 transform each into a single row looking like this example:

KIFI

1996M11

101.4

If I just expand the list it does not create the desired result but duplicate records, one for each item in the list:

How do I do this properly and get the two key values in seperte columns alongside the numeric value?

thanks for all help

[AntrikshSharma · Accepted solution]

@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]

 

 

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.

[steve1024]

Hi Antriksh, you are probely right. for some reason I am getting errors on the AddCustom, but maybe this is more relted to some errors in previous steps

here is the full code I am trying out:

let

  content = "{#(lf)  ""query"": [],#(lf)  ""response"": {#(lf)    ""format"": ""json""#(lf)  }#(lf)}",

  Source = Json.Document(Web.Contents("http://.../Indikatorm.px", [Content=Text.ToBinary(content)])),

  Navigation = Source[data],

  Table_1 = Table.FromList(Navigation, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

  Table_2 = Table.ExpandRecordColumn(Table_1, "Column1", {"key", "values"}, {"key", "values"}),

  Table_3 = Table.ExpandListColumn(Table_2, "values")

  ,

    AddedCustom = 

        Table.AddColumn (

            Table_3,

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

@steve1024 Yes, your Content step is messed up because of those line feeds #(lf), you probably removed something from the step inadvertently that broke it, I do that all the time when moving too quickly.

[steve1024]

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
KIFI	2022M10	123.3