Power Query runs ODBC fine, but Power BI folds incorrectly, gets an "Syntax Error" refreshing data

[rwmnau]

I have an issue with data folding on data from a specific ODBC data source - everything is fine in Power Query as I set up my data steps, but then once I try to import the result, Power BI gives me the following error. It seems like Power Query isn't folding the filter criteria into my query, but then once I try to import it, my criteria is getting folded in (incorrectly, causing the error):

I'm doing a direct table connection, not a query, and I only have a single filter step. When I check the "Applied Steps" in Power Query, I can see that the navigation step is being folded (as a "SELECT (all columns)" from that table), but the "Filtered Rows" step isn't using query folding ("View Native Query" is disabled):

When I click "Close & Apply", I get the original error where it's clearly trying to fold my filter into the query itself (and failing). I can avoid this behavior by including a step that can't be folded first (like lowercase), but I'm not sure why the data import is acting differently than the Power Query interface. What's going on here?

[rwmnau]

@lbendlin I haven't seen that option before. From the M query below, it looks like PQ is using the Odbc.DataSource function because I'm browsing the table and I want to take advantage of query folding in this case. I confirmed that if I switch my Source to use a query, it switches the function to Odbc.Query and disables any query folding. It does resolve my error, but I can also resolve it by just using a non-foldable step that forces folding to stop before the filter error step.

@v-yiruan-msft Here's what the M query (Advanced Editor) looks like - you can see that it's a few simple steps, and it's the final one that causes the issue. If I remove that step, the table imports without any issues - if it include this step, then Power Query claims it can't fold the step, but then loading the data into the Power BI report attempts to fold the filter into the query (incorrectly) and causes the error:

[v-yiruan-msft]

Hi @rwmnau ,

Sorry for delay. What value does the field USER_NM_DISABL_TN contain? What's its data type?  Whether it still get the error after removed the applied step "Filtered Rows"?

Best Regards

[v-yiruan-msft]

Hi @rwmnau ,

Could you please provide the full applied codes(mask sensitive data) in Advanced Editor in order to make troubleshooting? Did you use any parameter in filter step? And query folding is missing after applied the step "Filtered Rows"?

In addition, please review the content in the following links to get the details of query folding.

Not Folding; the Black Hole of Power Query Performance

Power BI - Checking Query Folding with View Native Query

Best Regards

[lbendlin]

Have you considered using ODBC.Query() instead?  Enabling DirectQuery for an ODBC-based Power Query connector | Microsoft Docs