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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
bsheffer
Continued Contributor
Continued Contributor

Prevent removal of lower case values on load

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

 

bsheffer_0-1638979694736.png

 

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

bsheffer_2-1638980119601.png

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?

 

 

1 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.

 

View solution in original post

3 REPLIES 3
StephenF
Responsive Resident
Responsive Resident

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

DataInsights
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.