Getting PowerPivot case sensitive

[Jan-Philipp]

Hey everyone,

I have a file with a column containing the same word/words a couple of times. The only way to distinguish these two is upper case or lower case style (e.g, "professional" vs. "Professional").

When importing this file into Power Pivot, I only got the lower case ones. The upper case data gets lower case as well. So unfortunately, Power Pivot does not allow for distinguishing between upper and lower case data.

Does anybody has an idea how to get this fixed or how to get Power Pivot case-sensitive?

Thanks a lot in advance!

Best,
JP

 

[Jacob Barnett]

JP

As far as I am aware your only chance is to deal with the issue as part of the import process by either creating another column to indicate what was in the source data or changing the source data to reflect the difference (eg Append the uppercase with a 'u' on the end).

If your data source is Excel then it's a piece of cake, tells you whether the contents of cell A1 are Uppercase:

=IF(EXACT(B2,UPPER(B2)),TRUE,FALSE)

If your data source is SQL then its less straightforward but is definitely possible. I pulled this code out of my SQL bag of tricks, its not rigorously tested but I think it will work.

CASE WHEN[COLOR=#191970][FONT=Verdana] thefield COLLATE Latin1_General_CS_AS=upper(thefield) THEN 'u' ELSE '' END[/FONT][/COLOR]

Jacob