Combine all columns from a row in 1 new column

[merijndk]

I have the following table:

ID | 1  | 2  | 3  | 4  |
0  | 11 | 12 | 10 | 18 |
2  | 14 | 13 | 19 | 20 |

Now I want to combine all rows after ID to 1 new row that has all the values with commas seperated, so:

ID | allow_rows  |
0  | 11,12,10,18 |
1  | 14,13,19,20 |

 How could I achieve this in the query editor?

[MarcelBeug · Accepted solution]

A more dynamic solution would be the following code:

let
    Source = Input,
    #"Added Custom" = Table.AddColumn(Source, "allow_rows", each Text.Combine(List.Transform(List.Skip(Record.FieldValues(_)),Text.From),",")),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"ID", "allow_rows"})
in
    #"Removed Other Columns"

[MetrumOBE]

Hello merijndk,

you can do it by adding a custom column in the Query Editor and then perform a concatenation like this :

= [1] & "," & [2] & "," ...

This is in the case you have a fix number of columns.

Or you can follow this post explaining how to merge several columns to create a merged one.

https://support.office.com/en-us/article/Merge-columns-Power-Query-80ec9e1e-1eb6-4048-b500-d5d42d9f0...

Or, you can also use the CONCATENATEX() DAX function into the Power BI Model.

I hope it will help you.