none
Dynamically append columns from the same table RRS feed

  • Question

  • Hi,

    There's a table with different number of columns. I would like to append all the columns into one. Sometimes there's two columns, three or four.


    Wednesday, March 21, 2018 11:43 AM

Answers

  • There are few ways to resolve this. One would be to add a column to the table with all the transformations inside

    = Table.AddColumn(tab,"Combined",each Text.Combine(List.Transform(Record.ToList(_),Text.From)))

    where tab is the table name and Combined is the new column name

    you can read the transformation 

    each Text.Combine(List.Transform(Record.ToList(_),Text.From))

    as

    1. for each row (_)

    2. get the values of all the columns (Record.ToList)

    3. transform them to text (List.Transform(...,Text.From))

    4. combine them (Text.Combine)

    You can also supply a second parameter to the function Text.Combine, as a delimiter.

    full example

    let
        tab = Table.FromRecords({  [CustomerID = 1, Name = "Bob", Phone = "123-4567"],  [CustomerID = 2, Name = "Jim", Phone = "987-6543"] ,  [CustomerID = 3, Name = "Paul", Phone = "543-7890"] ,  [CustomerID = 4, Name = "Ringo", Phone = "232-1550"]}),
        out = Table.AddColumn(tab,"Combined",each Text.Combine(List.Transform(Record.ToList(_),Text.From)))    
    in
        out

    Wednesday, March 21, 2018 2:32 PM