How to split column and show all unique values in a slicer

[Sean]

I have a column showing Street/Highway Names but sometimes if a location is at an intersection it shows 2 Street names

always separated by &

For example:

COLUMN

Street 1

Street 1 & Street 2

Street 1 & Street 3

Street 4

Street 5 & Street 6

Street 7

How can I create a single column to be used for a slicer that lists ALL unique Street names only.

SLICER

Street 1 => should pull up the intersections as well so 3 total locations

Street 2

Street 3

Street 4

Street 5

Street 6

Street 7

etc...

Thanks!

[austinsense · Accepted solution]

You're gonna have to use the query editor in Power BI to create a new table.  Here are the steps ...

** Start with the table that has the street names on it **

1) remove all columns except the unique row identifier and the street name column,

2) split your street name column on the "&", trim the results of all the columns

3) unpivot all the columns EXCEPT the unique row identifier into one column (so now you only have two columns - unique row identifier and street name),

4) select both columns and remove duplicate values - although you shouldn't have any duplicates

You can then take this table in the data model and then join it to the original table using the unique row identifier.  Make sure you set the relationship between the tables to be bi-directional.  Your new table is used for your slicer.

[Sean]

Thanks! Works great.