Forum Discussion

santu1021's avatar
santu1021
Icon for Helper II rankHelper II
6 years ago
Solved

eliminate duplicates from one cell

how to eliminate duplicates from one cell.

In below example "TELESCOPIC HANDLERS" is repeating serveral times. How to eliminate duplicates?

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI santu1021,

    You can take a look at the following methods if they meet for your requirements.

    #1. Power query custom column:

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.Distinct(Text.Split([ProdcutType],",")),","))

    #2. Dax calculated column:

    Distinct Product =
VAR _path =
    SUBSTITUTE ( [ProductType], ",", "|" )
VAR _lengh =
    PATHLENGTH ( _path )
VAR itemList =
    DISTINCT (
        SELECTCOLUMNS (
            GENERATESERIES ( 1, _lengh , 1 ),
            "ProductType", PATHITEM ( _path, [Value] )
        )
    )
RETURN
    CONCATENATEX ( itemList, [ProductType], "," )

    Regards,

    Xiaoxin Sheng

4 Replies

    • santu1021's avatar
      santu1021
      Icon for Helper II rankHelper II
      6 years ago

      Thank you !!! I want to eliminate repeating text and would like to see text only once.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        In Power Query:
        1. Add an Index.
        2. Split into columns on comma.
        3. Unpivot all the columns around the index.
        4. Remove duplicates.
        5. Pivot again.
        6. Remove Index.

         

        When pivoting use concatenation as the aggregation function.

         

        Best

        D

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI santu1021,

    You can take a look at the following methods if they meet for your requirements.

    #1. Power query custom column:

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.Distinct(Text.Split([ProdcutType],",")),","))

    #2. Dax calculated column:

    Distinct Product =
VAR _path =
    SUBSTITUTE ( [ProductType], ",", "|" )
VAR _lengh =
    PATHLENGTH ( _path )
VAR itemList =
    DISTINCT (
        SELECTCOLUMNS (
            GENERATESERIES ( 1, _lengh , 1 ),
            "ProductType", PATHITEM ( _path, [Value] )
        )
    )
RETURN
    CONCATENATEX ( itemList, [ProductType], "," )

    Regards,

    Xiaoxin Sheng