Prevent removal of lower case values on load

[bsheffer]

when I select distinct values from my sql server table I get this

PRODUCT_CODE
AV
BE
BH
BU
CD
DC
DT
EI
EP
GS
gs
HP
IL
MM
PA
PC
PE
PG
pg
PH
ph
PI
SD
SS
TP
tp
UP
VA
va
VF
VL
VM
vm
VN
VT
WC
WU

GS & gs, PG & pg, etc are distinct values and I want them to stay that way

when I load the table in power query the preview shows all 37 values in upper or lower case as correct from the data source

select * from bpsrc_TRIDENT_PRODUCT_CODES

but when I complete the load into the report view and display in a table, the lower case values have been forced to upper case

TP Tele-Pay (OBSOLETE) is the tp value in the source table

how do I get the load into model to not uppercase the lower case value?

[StephenF]

Its unbelievable this isnt documented heavily or there isnt a way to turn this behaviour off out of the box.

[DataInsights]

@bsheffer,

See the link below for a detailed explanation:

https://community.powerbi.com/t5/Issues/Text-auto-transforming-from-ALL-CAPS-to-lower-case/idc-p/206... 

[bsheffer · Accepted solution]

https://blog.crossjoin.co.uk/2019/10/06/power-bi-and-case-sensitivity/

Chris Webb does a better job I think.

To summarize, powerbi will take the first version of case and force all subsequent rows with the same value and force it into that case format

so if it encounters GS first then later appearing gs will be changed to GS.  Likewise if it encounters gs first then gs all the way.

Chris suggests that we use M code to add a non-appearing space to change these values.  His examples are just creating a table and not showing an import.  I import from sql server so adding the code to add a nonappearing space worked just fine.  The downside is that I have to either name all the columns explicitly because I'm just changing the one column or I have to add a new column with the appended value

This isn't a perfect code because I'm testing for an all lowercase value and adding the nonspacezero character.  Better code would only affect lower case values that had an uppercase counterpart.

select
case when product_code = lower(product_code) and not product_code = upper(product_code) then PRODUCT_CODE + nchar(8203)
else product_code end  as PRODUCT_CODE
,PRODUCT_DESC
,DISPLAY_ORDER
,D256_TERM_CAP
,TRIDENT_PAYMENT_GATEWAY
,PRODUCT_ID

from bpsrc_TRIDENT_PRODUCT_CODES

visually I can now see both values in  table visual even through the lowercase value is actually 3 characters long.  I would have to make sure this was carried throughout the model to keep from breaking relationships and other objects.