Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Did you hear? There's a new SQL AI Developer certification (DP-800). Start preparing now and be one of the first to get certified. Register now

KNP

Power Query Online (Dataflows) - not case sensitive

Submitted by Super User on ‎01-26-2022 11:34 AM

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.

 

KNP_0-1643225527256.png

 

Status: Investigating
See more ideas labeled with:
6 Comments (6 New)
Comments
v-robertq-msft
Community Support
Community Support
‎01-26-2022 06:19 PM
Status changed to: Investigating

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:

vrobertqmsft_0-1643249947425.png

 

In Power Query:

vrobertqmsft_1-1643249947428.png

 

In Power BI dataflow:

vrobertqmsft_2-1643249947432.png

 

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

KNP
Super User
Super User
‎01-27-2022 10:49 AM

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
Community Support
Community Support
‎01-27-2022 06:19 PM

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
Frequent Visitor
‎06-26-2024 12:54 AM

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
Super User
Super User
‎06-26-2024 01:22 AM

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
Frequent Visitor
‎06-26-2024 04:38 AM

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.

Idea Statuses