Forum Discussion

FCD's avatar
FCD
Regular Visitor
2 years ago
Solved

Add Variable quantity of columns and transpose data

Dear All,

I have a table called table1 with 2 differents columns (called Letter and Type)

I wish I can create a new table (table2) from this table containing in the first colum all the distinct Types and next as many columns as the maximum qty of different Letters in each type (n).

Each column will be called like KW1, KW2, …, KWn

Then the values of Types will be added in the different columns. If there is no value, the cell will remain empty.

 

See attached screenshot to show what I expect to get.

Thank you for help.

Best Regards

Need Help
  • slorin's avatar
    slorin
    2 years ago

    Hi FCD 

     

    = Table.Combine(
         Table.Group(Your_Source,
           {"Type"},
           {{"Data", each Table.FromRows(
             {{[Type]{0}} & [Letter]},
             {"Type"} & List.Transform({1..Table.RowCount(_)}, each "KW" & Text.From(_)))
           }})
        [Data])

     Stéphane

2 Replies

  • slorin's avatar
    slorin
    Super User
    2 years ago

    Hi FCD 

     

    = Table.Combine(
         Table.Group(Your_Source,
           {"Type"},
           {{"Data", each Table.FromRows(
             {{[Type]{0}} & [Letter]},
             {"Type"} & List.Transform({1..Table.RowCount(_)}, each "KW" & Text.From(_)))
           }})
        [Data])

     Stéphane

  • FCD's avatar
    FCD
    Regular Visitor
    2 years ago

    Thank you very much. Just what I expected