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

 

 

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