Forum Discussion

CPrince's avatar
CPrince
Frequent Visitor
6 years ago
Solved

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

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

Need Help
Tips and Tricks
Tutorial Requests
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

6 Replies

    • CPrince's avatar
      CPrince
      Frequent Visitor
      6 years ago

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

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        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,

         

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    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