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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sipati
Employee
Employee

Display case sensitive values in DirectQuery PowerBi

Sql Query - 

 

 

CREATE TABLE dbo.abctest (
    id int,
    [name] varchar(20)
);


insert into abctest(id, [name]) values (1, 'tree');
insert into abctest(id, [name]) values (2, 'Tree');
insert into abctest(id, [name]) values (3, 'TRee');
insert into abctest(id, [name]) values (4, 'tReE');

 

 


I am trying to create a DirectQuery PowerBi report, I am unable to preserve cases. All 4 values gets displayed as same.
Capture.JPG










I tried this solution - 
Chris Webb's BI Blog: Power BI And Case Sensitivity Chris Webb's BI Blog (crossjoin.co.uk)
It didn't work, as the queries convert to import mode.

I  tried this as well-
Create a Dimension Table with Power Query: Avoid the case sensitivity bug! – The BIccountant
But the issue remained, not sure why this is a recommended solution on many questions, Am I doing something wrong (What Am I supposed to do after creating "MyNewFactTable"? What does it mean by "Make sure to use the new “Index”-columns as your keys in your model."?)

Can anyone help please?

3 REPLIES 3
Aqila_Balqis
Frequent Visitor

Hello @sipati 

 

I am wondering if you found any solution regarding this matter.

 

I am having the same problem as yours where I am using direct query mode from Databricks, and the table visual converts my values to all upper case because it found the same title in upper case.

in my power query:

Aqila_Balqis_0-1697702656945.png

 

in my table visual

Aqila_Balqis_1-1697702678394.png

 

I tried suggestions from Chriss and BI Accountant blogs but as you mentioned it works if its in import mode.

 

I'm trying to solve this issue since last few days and haven't found any solution yet. Would appreciate if you can share with me how to solve it.

 

Thank you.

v-yangliu-msft
Community Support
Community Support

Hi  @sipati ,

 

You can try to select "query settings" in the right pane in power query, delete the "uppercase text" step, or go to the advanced editor to delete such information

#"Uppercased Text" = Table.TransformColumns(#"Changed Type",{{"Contract ID", Text.Upper, type text}})

 

There are also similar questions for you to check. I hope it can help you:

https://community.powerbi.com/t5/Desktop/Table-visualization-and-case-sensitivity/td-p/330455

https://community.powerbi.com/t5/Desktop/Ignore-Case-sensitive-data-in-a-table/td-p/531849

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi, Thanks for the reply. I don't have such steps which is manipulating the cases, I want to preserve the cases and display them as it is, but powerbi is unable to -

sipati_0-1606893531920.jpeg

Also, the actual field is going to be 50 characters long, so it is not possible to compare each character and append zero-width space for each lowercase/uppercase character to manipulate without changing storage mode to import (as Power Query might not support directquery)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.