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

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,