Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Merge rows based on duplicate ID field

Hi I am looking at trying to merge two field rows based on a duplicate value

ID

RefID (Duplicate Field)

Location (To Merge)

Area (To Merge)

1

1

UK

Cheshire

2

1

UK

Lancashire

3

2

USA

New York

4

2

USA

New York

5

2

UK

Cheshire

6

3

USA

New York

7

4

USA

New York

8

4

USA

Wisconsin

 

Output

ID

RefID

Location

Area

1

1

UK

Cheshire; Lancashire

3

2

USA; UK

New York; Cheshire

6

3

USA

New York

7

4

USA; USA

New York; Wisconsin

 

Also is it possible for the field to not have duplicate values such as “UK; UK; USA; UK; USA” but only have one unique value ie “UK; USA.

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Anonymous  - Try this Power Query script:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeNQbyDhnJFanJFZlKoUqxOtZIQs45OYl5yIkDMGCoHkQ4MdgaRfarlCZH5RNljKBLeUKUwKwy4zoIAxdk3mQAET7FIWKFLhmcXJ+XnFmXlKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, RefID = _t, Location = _t, Area = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"RefID", Int64.Type}, {"Location", type text}, {"Area", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"RefID"}, {{"All Rows For RefID", each _, type table [ID=number, RefID=number, Location=text, Area=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "ID", each List.First([All Rows For RefID][ID])),
    DistinctLocations = Table.AddColumn(#"Added Custom", "Location", each List.Sort(List.Distinct([All Rows For RefID][Location]))),
    ConcatenateLocations = Table.TransformColumns(DistinctLocations, {"Location", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    DistinctAreas = Table.AddColumn(ConcatenateLocations, "Area", each List.Sort(List.Distinct([All Rows For RefID][Area]))),
    ConcatenateAreas = Table.TransformColumns(DistinctAreas, {"Area", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Removed Columns" = Table.RemoveColumns(ConcatenateAreas,{"All Rows For RefID"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "RefID", "Location", "Area"})
in
    #"Reordered Columns"
    I hope this helps. If it does, please Mark as a solution.
    I also appreciate Kudos.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Try using

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

     

    If required also summarize function

    https://docs.microsoft.com/en-us/dax/summarize-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

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    You can do this in DAX using the following:

    Table 2 = SUMMARIZE('Table1',[RefID (Duplicate Field)],"ID",MIN('Table1'[ID]),"Location",CONCATENATEX(DISTINCT('Table1'[Location (To Merge)]),[Location (To Merge)],";"),"Area",CONCATENATEX(DISTINCT('Table1'[Area (To Merge)]),[Area (To Merge)],";"))

     

    ImkeF  should be able to provide a Power Query solution

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous  - Try this Power Query script:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeNQbyDhnJFanJFZlKoUqxOtZIQs45OYl5yIkDMGCoHkQ4MdgaRfarlCZH5RNljKBLeUKUwKwy4zoIAxdk3mQAET7FIWKFLhmcXJ+XnFmXlKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, RefID = _t, Location = _t, Area = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"RefID", Int64.Type}, {"Location", type text}, {"Area", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"RefID"}, {{"All Rows For RefID", each _, type table [ID=number, RefID=number, Location=text, Area=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "ID", each List.First([All Rows For RefID][ID])),
    DistinctLocations = Table.AddColumn(#"Added Custom", "Location", each List.Sort(List.Distinct([All Rows For RefID][Location]))),
    ConcatenateLocations = Table.TransformColumns(DistinctLocations, {"Location", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    DistinctAreas = Table.AddColumn(ConcatenateLocations, "Area", each List.Sort(List.Distinct([All Rows For RefID][Area]))),
    ConcatenateAreas = Table.TransformColumns(DistinctAreas, {"Area", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Removed Columns" = Table.RemoveColumns(ConcatenateAreas,{"All Rows For RefID"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "RefID", "Location", "Area"})
in
    #"Reordered Columns"
    I hope this helps. If it does, please Mark as a solution.
    I also appreciate Kudos.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Amazing this worked and have learnt more about Group By 🙂