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

 

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

     

     

5 Replies

  • 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
  • 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

     

     

    • steve1024's avatar
      steve1024
      Frequent Visitor
      3 years ago

      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's avatar
        AntrikshSharma
        Community Champion
        3 years ago

        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.

    • 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.