Power Query Online (Dataflows) - not case sensitive

[KNP]

I came across what I think is a bug while working on a Dataflow.

It appears that Table.SelectRows is not case sensitive in a Dataflow but is in Desktop. 

Obviously it is supposed to be, or at least it always has been until now.

I haven't had a chance to test other functions.

[v-robertq-msft]

Hi,

According to my test, the Table.SelectRows() function are case sensitive both in the Power Query and Power BI dataflow, here’s my data source and my test screenshots:

Excel data source:

In Power Query:

In Power BI dataflow:

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

[KNP]

Hi @v-robertq-msft,

I assume the XML/JSON comment above was pasted in the wrong post.

The behaviour I'm experiencing doesn't match your tests. Source is SQL Server (on-prem).

If you could test a Dataflow with on-prem SQL with a gateway that may produce different results?

Thanks.

[v-robertq-msft]

Hi, 

In my opinion, if you want to get a solution for this kind of data flow problem as soon as possible, I suggest you to open a support ticket to get direct help from the technical support team of Microsoft to have a remote session them to help you to make sure if this is an issue existing in the Power BI.

https://community.powerbi.com/t5/Community-Blog/How-to-create-a-support-ticket-in-Power-BI/ba-p/6830...

https://powerbi.microsoft.com/en-us/support/

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

[schloppyestates]

Hi @KNP 

Since you say that you are using a SQL server, what I think is most likely happening here is that your query is

being folded into a SQL Query that is delivered directly to the server, and that you are using a database collation that is case insensitive.

The filter expression [Status] = "no d" gets translated into a where filter that looks like this -

where [_].[Status] = 'no d' and [_].[Status] is not null

However the sematics of these two forms can differ, depending on the the collation being used for the database.

See this blog post for more info -

https://bengribaudo.com/blog/2021/07/23/5890/equals-is-not-always-equivalent-when-query-folding-does...

\Anders E. Andersen

[KNP]

Hi @schloppyestates ,

The problem is not with the folding of the query but the difference in behaviour between Desktop and Dataflow. The screenshot is not as obvious as it should have been, but the setup was identical between Desktop and Dataflow with different results. 

Anyway, it's 2 years ago and not an issue for me now. 

Thanks, 

Kim

[schloppyestates]

I understand. For what its worth, I see the same behaviour in desktop and dataflows, and excel for that matter, so I can't reproduce your issue. I think it is likely you have done something that broke query folding in your desktop example. That is the only explanation, that I can see, for how the results could be different.