Forum Discussion
PBI doesn't recognize same text in a column
Hello everyone,
First table is from my original dataset which I'm using look up value to create new column to another dataset. The problem is that when I do that, I end up having two exact text but power Bi is counting as if they were different:
Mapping Dataset :
Look up dataset :
I already use the TRIM , doesn't worked.
I hope you can help me. Thank you very much!
Hi DKGH,
Thank you for reaching out to Microsoft Fabric Community.
Since you are using calculated columns in power bi desktop so here is a DAX formula to normalize the text values:
CleanSegment =
SUBSTITUTE(
SUBSTITUTE(
TRIM([Summary Market Segment]),
UNICHAR(160),
""
),
UNICHAR(8203),
""
)
This removes the non breaking spaces and hidden characters that may have caused duplication like Other and ensures consistent matching across datasets.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
8 Replies
Hi DKGH,
I am not sure where this error is coming from, but it is likely happening for some purpose. But an easy solution for this is to go into your Power Query and simply do a replace values on the "Other" values so that any stray spaces, line breaks, tabs, etc. are removed. This may also help show the exact cell values as in a replaced values function the entire value is broken down into special characters (This will make more sense inside of the function).
Thanks!Hello DKGH
Use this DAX to remove any space
CleanText = SUBSTITUTE(CLEAN(TRIM([Summary Market Segment])), UNICHAR(160), " ")
Thanks,
Pankaj Namekar | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
Hi pankajnamekar25 ,
Thank you for your response, somehow the clean function does not work in both measure & column, I get a warning that clean is not function. I simply used data groups to collapse Other in same group, it's worked this way.
Hi DKGH,
Thank you for reaching out to Microsoft Fabric Community.
Since you are using calculated columns in power bi desktop so here is a DAX formula to normalize the text values:
CleanSegment =
SUBSTITUTE(
SUBSTITUTE(
TRIM([Summary Market Segment]),
UNICHAR(160),
""
),
UNICHAR(8203),
""
)
This removes the non breaking spaces and hidden characters that may have caused duplication like Other and ensures consistent matching across datasets.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
Hi DKGH,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Thanks and regards,
Anjan Kumar Chippa
