Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.
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.
Thanks! Works great.
User | Count |
---|---|
100 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |