Display case sensitive values in DirectQuery PowerBi

[sipati]

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.











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?

[Aqila_Balqis]

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:

in my table visual

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]

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.

[sipati]

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 -

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)