Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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"
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"
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.
Or, you can also use the CONCATENATEX() DAX function into the Power BI Model.
I hope it will help you.
User | Count |
---|---|
102 | |
84 | |
77 | |
70 | |
67 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |