Search code examples
powerbidaxpowerquerypowerbi-desktopm

Filter out unique values from the cell that contains multiple values in Power BI table

I have a column in Power Bi table 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, Springfield, Pueblo West", the new column should show "Springfield, Pueblo West".

  • if the multiple value is "Springfield, Springfield, Springfield", the new column should show "Springfield".

Is there a way to create a calculated column that solves this?

This is the mage of the column and wanted result in red colour.

enter image description here

You can find sample data in PBI format at the next GDrive LINK:

Solution

  • Click transform data from the ribbon. When in Power Query, click Add Column -> Custom Column. Paste in the following code:

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

    enter image description here

    enter image description here