Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Automatic Case Change Taking Place Unexpectedly

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

 

FaultLookupTable.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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:

 

FaultLookupCaseChange.png

Status: Accepted

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

Comments
v-robertq-msft
Community Support
Status changed to: Accepted

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
Employee
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
Not applicable

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"

 

matprest_0-1638352050063.png

 

jeroenterheerdt
Employee

@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
Employee

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
Not applicable

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.