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 valcat27
you can try this approach. First join Sales with product and expand needed column. Then join the client table. Don't expand it. Add a new column for principal client (you have to define what you mean by principal - in my example i used the maximum in clientID) and create a record for this. Add another column where you excluding the principal client from the other one joined using Table.RemoveMatchingRows and then combine the column cliendID with Text.Combine. Here the complete example... be aware that in this example I included in the first three steps your tables.
let
Sales = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SalesID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SalesID", Int64.Type}})
in
#"Changed Type",
ProductSales = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglIzjLGM4yAbKMwCxTOMsMyDIGs8whrFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProductID = _t, SalesID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ProductID", Int64.Type}, {"SalesID", Int64.Type}})
in
#"Changed Type",
ClientSales = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglIyDLCMwyBrKMwSwTuKwpXMwMzjKHsyzgLEu4KYYGEM2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ClientID = _t, SalesID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", Int64.Type}, {"SalesID", Int64.Type}})
in
#"Changed Type",
JoinProductSalesSales = Table.NestedJoin
(
ProductSales,
"SalesID",
Sales,
"SalesID",
"Sales",
JoinKind.LeftOuter
),
#"Expanded Sales" = Table.ExpandTableColumn(JoinProductSalesSales, "Sales", {"SalesID"}, {"SalesID.1"}),
JoinProductSalesSalesWithClientSales = Table.NestedJoin
(
#"Expanded Sales",
"SalesID",
ClientSales,
"SalesID",
"Client",
JoinKind.LeftOuter
),
#"Added Custom" = Table.AddColumn(JoinProductSalesSalesWithClientSales, "GetPrincipalClient", each Table.Max([Client],"ClientID")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "GetOtherClients", each Text.Combine(List.Transform(Table.RemoveMatchingRows([Client],{[GetPrincipalClient]})[ClientID], each Text.From(_)), ", ")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Client"}),
#"Expanded GetPrincipalClient" = Table.ExpandRecordColumn(#"Removed Columns", "GetPrincipalClient", {"ClientID"}, {"ClientID"})
in
#"Expanded GetPrincipalClient"The output is this
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello Jimmy801 ,
Can you tell me exactly which formula should I put when creating the custom column for values combined (GetOtherClients column)?
Hello valcat27
to apply this when you have 3 Mio rows you can get hard times, but give it a try. However, once you have combined your dataset with your clientsales table you can add a new column to make your calculation out of the joined table and the maximum value used for your principal client.
Here the formula I used
Text.Combine(List.Transform(Table.RemoveMatchingRows([Client],{[GetPrincipalClient]})[ClientID], each Text.From(_)), ", ")
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHello Jimmy801 ,
I'm sorry, but it gave me an error.
Just to recap...I imported from SQL Server one table that contains: Sales table, Product table and Client table, including a column with the maximum value of clientID for each sale and a column with all ClientID's.
In your formula, what does "Client" refer to?
Moreover, does "GetPrincipalClient" refer to the column with the max ClientID and does "ClientID" refer to the column with all ClientID's?
Thank you for your help,
Hello valcat27
I don't have any variable called "client". The only thing I'm going to create is a joined table that i call "client" that is in fact the joined table "ClientSales".
Yes, the column GetPrincipalClient is exporting the maximumg of client ID from the client table as a record. This information is then used to create the GetOtherClients-column.
The clientID is coming from your dataset
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy