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

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

santu1021 what you expect if there are two distinct values or it will never happen?

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