Forum Discussion
Aggregate multiple rows into a single row, separating values by semicolon
Hi valcat27 ,
Try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0ABFKsTpIXBNULpAA8Y2ATEsgNkLhWYJ5xkCWORjHxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SalesID = _t, ClientID_max = _t, ClientID_all = _t]), #"Added Custom" = Table.Group(Source,{"SalesID","ClientID_max"}, {{"Column", each Text.Combine([ClientID_all], ","), type text}}) in #"Added Custom"Reference:
Power Query - Combine rows into a single cell - Excel Off The Grid
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Jimmy801,
Finally I could get some results, but not what I wanted.
I couldn't get any results with my data, maybe due its size. So, I tried with a simpler and smaller dataset.
1 - I imported from SQL Server a table with the columns: SalesID, ClientID_max (maximum ClientID for that sale) and ClientID_all (all ClientID for that sale).
This is an example of that table:
After that, I tried to create a custom column using your formula, but I think it returns me a column whose all rows have the same value and this value corresponds to the combination of all clientID's independently of the SalesID. Moreover, I still have duplicate values for the same SalesID.
This is my advanced editor:
let
Source = Sql.Database(…),
#"Custom Column" = Table.AddColumn(Source, "others_clients", each Text.Combine(List.Transform(Table.RemoveMatchingRows(Source,{[ClientID_max]})[ClientID_all], each Text.From(_)), ", "))
in
#" Custom Column"
2 -I also tried another approach. I imported another table with only the first two columns (SalesID and ClientID_max) to avoid the duplicate values that ClientID_all column was causing. Then I joined the first table that contains the ClientID_all and I did not expand it (as you have indicated). When I tried to create the custom column, I couldn't do it as I didn't expand the table, its columns are not available to select, just the table name is available.
Can you find what I have done wrong ?
Thanks in advance
Hi valcat27 ,
Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0ABFKsTpIXBNULpAA8Y2ATEsgNkLhWYJ5xkCWORjHxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SalesID = _t, ClientID_max = _t, ClientID_all = _t]),
#"Added Custom" = Table.Group(Source,{"SalesID","ClientID_max"}, {{"Column", each Text.Combine([ClientID_all], ","), type text}})
in
#"Added Custom"
Reference:
Power Query - Combine rows into a single cell - Excel Off The Grid
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.