Forum Discussion

arinyc's avatar
arinyc
Icon for Helper I rankHelper I
3 years ago
Solved

Filter out unique values from the cell that contains multiple values

Hi,   I have a column that contains multiple values and I want to create a new column that keeps the unique values only from the original one.  Example: - if the multiple value is "Springfield, S...
Need Help
Tips and Tricks
  • lazarjo's avatar
    lazarjo
    3 years ago

    You can solve this by adding a custom column in Power Query. On the "Add Column" tab click on Custom Coulmn option and type the next code :

     

     

    Text.Combine( List.Distinct( List.Transform( Text.Split([City],","), each Text.Trim(_))),",")

     

     

     

    The result will be unique values separated by comma in each cell.

     

     

    Best

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
3 years ago

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(List.Distinct(Text.Split([City],", "),Comparer.OrdinalIgnoreCase),", "))
in
    #"Added Custom"

Hope this helps.