Forum Discussion
Aggregate multiple rows into a single row, separating values by semicolon
Hello all!
I would like to merge three tables, but avoiding some duplicated rows without losing some information.
The tables are Sales table, Product table and Client table and they are imported from SQL Server. Note that, one sale can have more than one product and more than one client associated.
Considering one sale, I want to keep the original rows for each product but I just want one row for all clients. For example, to keep all the information for the “principal client” and one column with all other client ID’s related.
My idea is to import two separate tables:
1) one table with the join of sales table, product table and client table with only the “principal client” information, like this:
2) another client table with all the clients, excluding the “principal client”, like this:
On this second imported table, I would like to apply something like a group by function (in Power Query) which will give me as output something like this:
After that, I want to join that output with the first imported table.
Thus, this is the wanted output:
Can anyone help me? If you know a better approach to get the same result, I would be very grateful.
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.
15 Replies
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
JimmyHello Jimmy801 ,
Thank you for your answer.
I still couldn't confirm the solution because I have not much experience working with power bi and because the client table join is taking many hours (probably because my tables have more than 3 000 000 rows). I'm trying to import the three tables already merged from SQL SERVER and apply the transformations that you indicated later in the power query.
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
Jimmy
No, it does not work. After an error related with invalid html, it returns the error: Correct the highlighted errors and try again. Post flooding detected (community received post of unique message more than 1 times within 3,6 seconds)
There must be something strange going on then. Another option is to just upload the excel file with the tables to the cloud (Dropbox, Onedrive...) and share here the URL to the file. Or just upload the file to a site like tinyupload.com (no sign-up required).
Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
