Merge rows for more than one level of duplicated data

[Anonymous]

Hello,

I've managed to merge + group by line break to one level of duplicative data (Location, phone number) to form the following: 

Name	Location	Phone number	Purchase	Sales Rep
Client A	NY	12345	Product 1 Product 2 Product 3	Rep 1 Rep 2 Rep 1
Client A	OH	16283	Product 2 Product 3 Product 4	Rep 5 Rep 2 Rep 3
Client B	CA	13340	Product 4 Product 4 Product 6	Rep 7 Rep 2 Rep 3
Client B	TX	19876	Product 1 Product 5 Product 6	Rep 3 Rep 1 Rep 5

And would like to check if the next level of duplicative data (Name) can be merged as follows:

Name	Location	Phone number	Purchase	Sales Rep
Client A	NY	12345	Product 1 Product 2 Product 3	Rep 1 Rep 2 Rep 1
OH	16283	Product 2 Product 3 Product 4	Rep 5 Rep 2 Rep 3
Client B	CA	13340	Product 4 Product 4 Product 6	Rep 7 Rep 2 Rep 3
TX	19876	Product 1 Product 5 Product 6	Rep 3 Rep 1 Rep 5

Thank you.

[v-janeyg-msft]

Hi, @Anonymous 

It’s my pleasure to answer for you.

According to your description, I think you may do some transformations to meet your requirement.

You can add a custom column to replace the raw ‘name’ column.

Like this:

Here is my .pbix file. Hope this helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Janey @v-janeyg-msft 

Thank you for the solution, I cannot seem to open your PBI file due to versioning issues. 

Do I need to include a pre-defined Index column, because when I include the same custom column I get an error saying the Index is not found. 

Alternatively, perhaps you can share the full transformation code here that would be great.

Thanks again!

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

Yes, you need to create an index column first, and then create a custom column. After the customization is completed, you can delete the original 'name' column, and then rename the custom column to 'name'.

Here are my sample code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7JTM0rUXBU0lHyiwQShkbGJqZAOqAoP6U0ucRYISi1wDAmD8o1AnGN4FywrLFSrA6KOf4eQMLIxNTMHGEOWKMpmkY41wTDHCegVmeQYSampmZmCHPAKs1RNRoRMCckAkiYAs1B8pchqgNMUb1pBnatUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Location = _t, #"Phone number" = _t, #"Purchase#(lf)#(lf)Sales Rep" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Location", type text}, {"Phone number", Int64.Type}, {"Purchase#(lf)#(lf)Sales Rep", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let 
tab = Table.Group(#"Added Index", {"Name"}, {{"Custom", each List.Max([Index]), type number}})
in 
if 
List.Contains(tab[Custom],[Index])
then 
"" 
else 
[Name]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Name"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Location", "Phone number", "Purchase#(lf)#(lf)Sales Rep", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "Name"}})
in
    #"Renamed Columns"

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @v-janeyg-msft 

The indexing part helped to address the issue. Appreciate the help!

[CNENFRNL]

Hi, @Anonymous , do you need such a conversion?

If so, Pivot table in Excel is enough to do the trick with ease after a bit of very fundamental transformation in Power Query. You might want to refer to the attach Excel for details.

[Anonymous]

Thanks @CNENFRNL , however my main table has about 20 data columns so it becomes less wieldy when stacked up in a pivot table and I was hoping for a way to make the changes directly via Power Query.

[Anonymous]

Sorry the HTML formatting makes the table look rather convoluted. The second table is the same as the first, with both Client A rows merged & both Client B rows merged.