For Power Query "abc" and "ABC" are different strings. For Power BI (DAX), they are not.
This can lead to a situation where a Power Query table with distinct entries turns into a table in Power BI with non-distinct entries – and that can break a 1:n relationship depending on it.
Let's assume you have a column with customer names: Peter, Paul, Mary, PETER
For Power Query this list is distinct. If you load it into Power BI, it is no longer distinct.
But did you know what the column would actually look like in Power BI?
It's not "Peter, Paul, Mary, PETER".
It's Peter, Paul, Mary, Peter".
"PETER, Paul, Mary, Peter" would be "PETER, Paul, Mary, PETER"!
That's something the VertiPaq engine does. If there is only one entry such as ALEXANDER, the capitalization is kept. If there is more than one entry that the engine considers to be the same, the spelling of the first occurrence is typically applied to all entries.
So "PETER, Paul, Mary, Peter" would be displayed as PETER, Paul, Mary, PETER.
For first names it might be obvious that PETER is just a misspelling. But what about company names? "ABC Ltd." and "abc Ltd." don't necessarily refer to the same company. Most companies care a lot about the correct spelling of their name – think of eBay, PayPal or IBM.
What if you absolutely need the exact spelling in Power BI, including upper and lower case?
➡️ As far as I know, there is no way to change the behavior of VertiPaq
➡️ But you can add invisible characters to every relevant name, such as a zero-width space (U+200B)
➡️ The exact logic – how many and where to place them – depends on your needs
Transforming your customer names this way leads to a much better result:
Beyond the correct display of strings, this simple scenario shows once again that you should avoid using strings as unique identifiers or keys.
💡The first choice should always be a numerical unique ID from your source system – a Customer ID in the example above. If there is no such ID, generate one in Power Query. Measures like DISTINCTCOUNT should be based on such a unique identifier.
❓ Do you know other ways to keep the exact spelling of names? What's your experience with case sensitivity in Power BI?
Super User