Merge duplicate rows while pivoting/retaining unique data from another column?

[CPrince]

Hi,

So I have a data set with a varying number of duplicates in the same column.

Column 1 | Column 2

N1 | 123

N1 | 124

N2 | 125

N2 | 126

N2 | 127

N3 | 128

N3 | 129

N4 | 130

N4 | 131

N4 | 132

I'm trying to condense the duplicates values together in column 1, while spreading the the unique values in column 2 across the columns (or alternatively merging them together by delimiter). 

So it would look something like this:

N1 | 123 | 124

N2 | 125 | 126 | 127

N3 | 128 | 129 

N4 | 130 | 131 | 132

Alternatively (Though I figured this would be easily accomplishable using merge):

N1 | 123;124

N2 | 125;126;127

N3 | 128;129

N4 | 130;131;132

[Jimmy801]

Hello

use Table.Group with Text.Combine as function for the grouping

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jNU0lEyNDJWitWBc0wgHCMwxxSZY4bMMYdwjMEcC2SOJYRjAuIYGyBzDJE5RkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Spalte1 = _t, Spalte2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Spalte1", type text}, {"Spalte2", type text}}),
    Group= Table.Group(#"Changed Type", {"Spalte1"}, {{"Combine", each Text.Combine([Spalte2], "; "), type text}})
in
    Group

  Have fun

Jimmy

[v-lid-msft · Accepted solution]

Hi @CPrince ,

We can do the following steps in Power Query Editor to meet your first requirement:

1. Group by the Column1

2. add the custom column

Table.Skip(
    Table.Transpose([OtherColumns]),
    1
)

3. Expand the new column

If you want to merge them into one column, we can add a custom culumn like following:

Text.Combine(
    Table.ToList(
        Table.TransformColumnTypes(
           Table.SelectColumns([OtherColumns],{"Column2"}),
           {{"Column2", type text}}
        )
    ),
    ", "
)

All the Queries:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jNU0lEyNDJWitWBc0wgHCMwxxSZY4bMMYdwjMEcC2SOJYRjAuIYGyBzDJE5RkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"OtherColumns", each _, type table [Column1=text, Column2=number]}}),
    Test = Table.AddColumn(#"Grouped Rows", "MergeTable", each Table.Skip(
    Table.Transpose([OtherColumns]),
    1
)),
    #"Expanded MergeTable" = Table.ExpandTableColumn(Test, "MergeTable", {"Column1", "Column2", "Column3"}, {"MergeTable.Column1", "MergeTable.Column2", "MergeTable.Column3"})
in
    #"Expanded MergeTable"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8jNU0lEyNDJWitWBc0wgHCMwxxSZY4bMMYdwjMEcC2SOJYRjAuIYGyBzDJE5RkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"OtherColumns", each _, type table [Column1=text, Column2=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine(
    Table.ToList(
        Table.TransformColumnTypes(
           Table.SelectColumns([OtherColumns],{"Column2"}),
           {{"Column2", type text}}
        )
    ),
    ", "
))
in
    #"Added Custom"

Best regards,

[Anonymous]

Hello,

Thanks for the reply, I'm lloking for a similar problem but with multiple rows, I tried your solution but it looks like it's specifice to the question asked, hence I'm modifying it a little. Please help:

So I have a data set with a varying number of duplicates in the same column.

Column 1 | Column 2

N1 | 123 |abc |xyz

N1 | 124 |def |uvw

N2 | 125 |ghi |rst

N2 | 126 | jkl | opq

I'm trying to condense the duplicates values together in column 1, while merging them together by delimiter

So it would look something like this:

N1 | 123, 124 | |abc |xyz

N1 | 123, 124 | |def |uvw

N2 | 125, 126 | ghi |rst

N2 | 125, 126 | | jkl | opq

[amitchandak]

Try CONCATENATEX

https://docs.microsoft.com/en-us/dax/concatenatex-function-dax

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[CPrince]

Sorry posted this in the wrong place. Thanks for the reply though!

[v-lid-msft]

Hi @CPrince ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,