Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I have run into a bit of an issue with a model I am working on in Power BI Desktop. I have connected to AD and have a column displaying users email address. I've split this column by the "@" delimiter so that the domain is showing in its own column. When I try to filter based on this column, it is showing duplicate entries in the filter. Example (reference the table below), if I try and filter on domain, I see 2 options for "emailA.com" instead of it showing up just once and when clicked, seeing all users with that domain. I've done a Trim and Clean on the column, with the same results. I've also tried formatting the column to all lowercase, with the same results.
Any thoughts?
Name | Domain |
J Smith | emailA.com |
N Johnson | emailB.com |
A Cooper | emailA.com |
C Michales | emailC.com |
Solved! Go to Solution.
Afte further investigation, there appears to be zero-width spaces in the data (e2808b hex). I cannot seem to get that removed, even when performing a "clean" format on the column.
What I ended up doing was a replace on the column and replacing "#(200B)" with "". This trimmed out the non-printable characters in the dataset and resolved the issue.
When you say "filter" do you mean a slicer? I entered the same data and the slicers are working fine for me. Can you give further details on what you're trying to do?
Essentially, yes a slicer. But even if I try and set a filter in the query editor, I have the same results. This was not the exact data set either, it was more to provide some context around the issue. What I would like to do is have a slicer where individuals could filter and run reports to see users who have a certain email domain. Maybe this will help with explaining, the items in red are all the same value - I would expect that to only show once in the slicer:
If you are creating the slicer by using the "domain" field directly from the table there is no reason for it not to work correctly. You're not using any calculations aside from the splitting of columns, are you? Have you doublechecked that there are no extraneous spaces on any of the values (create a new calc column that is CONCATENATE("***", CONCATENATE([Domain], "***") ) and put that in the slicer). Can you show the "Fields" tab from your PBI file?
Have you updated your PBI desktop recently? Or maybe a reinstall is necessary if nothing else helps.
My thoughts exactly. I have done a Trim, which should remove any extra spaces. When I export into excel, I can see that there are not spaces or extra characters, yet I have the same results in Excel with the duplicates when trying to filter the column. I am on the latest version of PBI Desktop and have tried on another PC with the same results.
Thanks.
Hi @justincooper88,
In Query Editor, please right click the [Domain] column and select 'Remove Duplicates'. Check whether the total of rows is reduced. If those duplicate records still exist, I think you should check the underlying source data to make sure each same domain is stored in same format.
Here is a similar thread: Same Values in Slicer
Regards,
Yuliana Gu
Hi,
When I removed duplicates, those duplicates still displayed in the slicer. I've checked the underlying source and they are of the same format - there isn't a way they could be stored as a different format.
Afte further investigation, there appears to be zero-width spaces in the data (e2808b hex). I cannot seem to get that removed, even when performing a "clean" format on the column.
What I ended up doing was a replace on the column and replacing "#(200B)" with "". This trimmed out the non-printable characters in the dataset and resolved the issue.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
94 | |
77 | |
63 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |