Automatic Case Change Taking Place Unexpectedly

[Anonymous]

When using Get Data in Power BI and entering case sensitive text, the data in the data model is correctly stored:

However when the data is loaded, the case status values change unexpectedly and this action is not recorded as part of Applied Steps so can't be modified or disabled:

[v-robertq-msft]

Hi,

I have reported this issue internally, ICM: 272940847

I will update here once I get any information.

Best Regards,

Community Support Team _Robert Qin

[jeroenterheerdt]

This is by design. Allow me to explain. Power BI Desktop, Power BI Service and Azure Analysis Services are case-insensitive with no option to change that (also no plans to provide this capability). However, the engine does not store everything upper case or lower case, it keeps the original format, but when making comparisons the case is ignored. As a result, the engine will store the first way of writing each string and then compare any other strings to it. If the other strings are the same (ignoring case) then the engine will just treat them as the same. So in the example given there are two strings in Column1 (ignoring case): FAULT and NO FAULT. The engine comes across FAULT first, and then compares the second row ('Fault') against, it ignoring case. Hence, 'FAULT' from row 1 is stored and is referred to in row 2. For 3 has 'No Fault' in Column1. That's not equal to 'FAULT', so the engine stores it. For 4 and 5 are compared against the strings the engine has stored so far and are equal to 'No Fault' it has stored (again ignoring case). As a result, the engine only really stores 'FAULT' and 'No Fault' and links the others to those. That's why, after loading Desktop just shows those two. The reason these are what they are is just because they appeared first in the dataset. If you change the first 'No Fault' into 'NO FAULT' that would be the string it stores and all 'No Faults' (row 3 and 5) would turn to that after the data has loaded.

[Anonymous]

Thanks for the detailed response and background @jeroenterheerdt 

Unfortunately the "Remove Duplicates" option is case sensitive - this means that it's not possible to deduplicate the columns where different cases exist in the string leading to the chance of having identical duplicate rows in the data model because the item is standardised. Appreciate, most of the time, the string could be converted to the same case before a duplication step but for niche cases where capitalisation may be important such as in names and passwords this could be a problem.

Please can the "Remove Duplicates" function be changed to be case insensitive and therefore align with the data model process or at least have an option to do this?

Here's a quick example of the issue in action copied from the Advanced Editor in Power Query Editor:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSksszSlRitWJVnKDs/zyFRCcPGQOVHUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Status = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Status", type text}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type")
in
#"Removed Duplicates"

[jeroenterheerdt]

@Anonymous that is because you're in PowerQuery and that part of the Power BI world is case sensitive. I will pass along this feedback!

[jeroenterheerdt]

by the way, if anyone wants more info, here's a docs page on it: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-data-types#text-type

[Anonymous]

Thanks @jeroenterheerdt, appreciate you looking into it for me and providing the link which articulates the issue better than I did. Having an additional option to ignore case when deduplicating in PowerQuery would resolve the issue but I don't know whether this is the right forum to request this as you're indicating a different team look after that aspect. Thanks for passing on the feedback.