Powerbi Ignoring Tab characters

[wilson_smyth]

I have a dimension that is polulateded using a select distinct on a text column, something like:

select distinct UPPER(colname) from tablename)

PowerBI is telling me a duplicate in the dimension on the one side of the relationship. Investigation shows that there is a value that is almost a duplicate, but one has a tab at the end, the other has not ( e.g. below, where -> represents the tab as i cant paste a tab into the forum).

Value

Value->

So it seems that Power query recognises the tab, and loads the value, but then outside of powerquery, powerbi does not recognise the tab, and sees the value as a duplicate value in a dimension.

To work around this I have had to:

-in Power query - Clean text (remove unprintable chars from the string)

-in Power query - Remove Duplicates.

Is there a disconnect between how powerQuery and PowerBi deal with non printable characteres?

[v-yuta-msft]

@wilson_smyth ,

You can go to transform tab -> click replace values to replace the unprintable values to printable values:

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[d_gosbell · Accepted solution]

---

@wilson_smyth wrote:

Is there a disconnect between how powerQuery and PowerBi deal with non printable characteres?

---

Yes, the tabular model strips trailing whitespace characters when it stores the data, Power Query does not do this.

Your workaround is a valid way of dealing with this scenario.

[wilson_smyth]

@d_gosbell thank you for confirming. Its good to understand the issue.