Aggregate multiple rows into a single row, separating values by semicolon

[valcat27]

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.

[Jimmy801]

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

[valcat27]

Hello @Jimmy801 ,

Can you tell me exactly which formula should I put when creating the custom column for values combined (GetOtherClients column)?

[Jimmy801]

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

[valcat27]

Hello @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,

[Jimmy801]

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

[valcat27]

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

[Icey · Accepted solution]

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.

[valcat27]

Hello @Icey ,

Thank you very much! It works!

[valcat27]

Hello @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.

[AlB]

@valcat27 

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.

[AlB]

@valcat27 

If you have the tables in Excel (small ones) and just copy them and paste here it should work

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.

[valcat27]

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)

[AlB]

Hi @valcat27 

Can you share the initial tables in text-tabular format instead of on pics? So that the contents can be copied...

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.

[valcat27]

Hello @AlB ,

Sorry, but it returns an error that I do not know how to solve. 

I do not have anything highlighted and I also have tried more than once. 

[valcat27]

The error is related with invalid HTML and post flooding..