Forum Discussion
Solved
Combine rows based on unique id
Hi, This should be easy to achieve, yet I'm breaking my head over it. I have a table like this: id branche 101 business 101 hr 102 business 103 business 103 finance ...
Here is the Power Query "M" code version:
let Source = Csv.Document(File.Contents("C:\temp\powerbi\group.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"id", type text}, {"branche", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"Branches", each _, type table}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([Branches][branche],",")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Branches"}) in #"Removed Columns"Basically, group the rows but keep all as the aggregation, return a table for "branche". Then, do a text combine on that table to get all of the values formatted as a single list of values with a comma separator.
Create a DAX measure like this:
Branches = CONCATENATEX(VALUES('group'[branche]),[branche],",")Put "id" column and "Branches" measure in a table visualization and set your "id" column to "Do not summarize".
Or, did you want this in an "M" solution?
