Forum Discussion
DirectQuery FoldingWarning "UNSEARCHABLE" despite SQLColAttribute returning SQL_PRED_SEARCHABLE
We are building a custom Power BI connector using Odbc.DataSource for my ODBC (TIBCO(R)/Spotfire(R) Data Virtualization (TDV/SDV)) ODBC driver. DirectQuery mode fails with:
OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression
to the data source. Please try a simpler expression.
The trace logs show OdbcQuery/FoldingWarning with:
Data Type of column customerid with searchable property UNSEARCHABLE should be
SEARCHABLE or ALL_EXCEPT_LIKE. You can override the supported data types from
ODBC driver using SQLGetTypeInfo.
Power BI Desktop version: 2.155.385.0
Column types: customerid = SQL_INTEGER (4), city = SQL_VARCHAR (12)
What we verified:
- SQLColAttribute(SQL_COLUMN_SEARCHABLE = field 13) returns 3 (SQL_PRED_SEARCHABLE) for ALL columns
- SQLGetTypeInfo natively returns SEARCHABLE=3 for all types including integer and varchar
- We implemented SQLGetTypeInfo = (types) => Table.Buffer(Table.TransformColumns(types, {{"SEARCHABLE", each 3}})) — the override fires BEFORE the FoldingWarning (confirmed by trace timestamps), but the warning fires anyway
- The trace shows ObjectCache/Metadata/CacheStats/Access entries BEFORE the FoldingWarning, suggesting Power BI checks an internal cache rather than the modified SQLGetTypeInfo result
Stack trace from OdbcQueryDomain/ReportFoldingFailure:
Microsoft.Mashup.Engine1.Library.Odbc.OdbcQuery.Sort(TableSortOrder, Boolean)
Microsoft.Mashup.Engine1.Library.Odbc.OdbcQuery.Group(Grouping)
Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query, Func`2)
Questions:
- What exactly does OdbcQuery.Sort/Group use to check column searchability — SQLGetTypeInfo SEARCHABLE, SQLColAttribute(SQL_DESC_SEARCHABLE), or an internal ObjectCache?
- Is there any SqlCapabilities or SQLGetInfo setting in the connector that controls what gets cached and checked?
- Has anything changed in Power BI 2.155.x regarding how ODBC type searchability is checked for custom connectors?
We've been stuck on this for two full days. Any help is greatly appreciated.
Hi,
Based on the checks you’ve already completed, this does not look like a normal driver metadata issue. Your driver returns SQL_PRED_SEARCHABLE from both SQLColAttribute and SQLGetTypeInfo, and your SQLGetTypeInfo override is being executed, yet the folding engine still classifies the columns as UNSEARCHABLE.
Odbc.DataSource builds its folding behavior from several sources, including driver capabilities, SQL type metadata, SQLGetInfo, and connector overrides. Microsoft documents SQLGetTypeInfo, SqlCapabilities, and related Odbc.DataSource options as supported ways to influence generated SQL and folding behavior. However, the internal logic used by OdbcQuery.Sort and OdbcQuery.Group, including any metadata caching, is not publicly documented.
A few things may help isolate the issue:
Test with a completely new PBIX file after clearing Power BI Desktop’s local cache and trace folders. This can help determine whether stale metadata is being reused.
Change the connector’s data-source path or DSN temporarily so Power BI treats it as a new source and rebuilds its metadata.
Confirm the driver also returns searchable metadata correctly through the ODBC descriptor field corresponding to SQL_DESC_SEARCHABLE, not only the older SQL_COLUMN_SEARCHABLE identifier.
Capture the complete ODBC trace and verify which metadata calls Power BI actually makes before the failure.
Test a minimal table containing only one SQL_INTEGER and one SQL_VARCHAR column, then apply equality filtering, sorting, and grouping separately. Microsoft’s ODBC troubleshooting guidance recommends testing folding operations individually across data types.
Try the same connector and driver with an earlier Power BI Desktop build. If it works there but fails in 2.155.385.0, that would strongly suggest a regression.I don’t see a documented SqlCapabilities or SQLGetInfo option that directly overrides per-column searchability for Sort or Group. SqlCapabilities mainly controls broader SQL grammar and supported operations; it may not replace the type-level searchability value used internally by the folding engine.
Since the trace shows the corrected SQLGetTypeInfo result before the warning, this may be an internal metadata-cache issue or a regression in the current Power BI Desktop ODBC folding implementation. I would recommend creating a minimal reproducible connector and reporting it through Microsoft support or the Power Query SDK repository, including:
The .mez connector
A minimal driver or trace
The failing M query
The full OdbcQuery/FoldingWarning
Results from both the current and an earlier Desktop versionIt would also be useful to know whether a simple equality filter folds successfully and only Table.Sort/Table.Group fail, or whether all predicates are rejected. That distinction could show whether Power BI is treating the column as unsearchable globally or only within specific folding operators.
4 Replies
Hi asingh1-tibco - One additional thing to check is whether this behavior is reproducible with a built-in ODBC connector against the same driver (without the custom .mez connector). If the issue occurs there as well, it would point more toward the driver or Power Query's ODBC layer.
If it only occurs with the custom connector, then the connector configuration (such as capability overrides or metadata handling) becomes the more likely cause.
Also, if you haven't already, try testing with a very simple query (for example, SELECT customerid FROM table) and verify whether filter, sort, and group fold independently.
That can help narrow down whether the issue is specific to Table.Sort/Table.Group or affects all searchable predicates.
I hoe this helps.
Hi asingh1-tibco
Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.
Hi,
Based on the checks you’ve already completed, this does not look like a normal driver metadata issue. Your driver returns SQL_PRED_SEARCHABLE from both SQLColAttribute and SQLGetTypeInfo, and your SQLGetTypeInfo override is being executed, yet the folding engine still classifies the columns as UNSEARCHABLE.
Odbc.DataSource builds its folding behavior from several sources, including driver capabilities, SQL type metadata, SQLGetInfo, and connector overrides. Microsoft documents SQLGetTypeInfo, SqlCapabilities, and related Odbc.DataSource options as supported ways to influence generated SQL and folding behavior. However, the internal logic used by OdbcQuery.Sort and OdbcQuery.Group, including any metadata caching, is not publicly documented.
A few things may help isolate the issue:
Test with a completely new PBIX file after clearing Power BI Desktop’s local cache and trace folders. This can help determine whether stale metadata is being reused.
Change the connector’s data-source path or DSN temporarily so Power BI treats it as a new source and rebuilds its metadata.
Confirm the driver also returns searchable metadata correctly through the ODBC descriptor field corresponding to SQL_DESC_SEARCHABLE, not only the older SQL_COLUMN_SEARCHABLE identifier.
Capture the complete ODBC trace and verify which metadata calls Power BI actually makes before the failure.
Test a minimal table containing only one SQL_INTEGER and one SQL_VARCHAR column, then apply equality filtering, sorting, and grouping separately. Microsoft’s ODBC troubleshooting guidance recommends testing folding operations individually across data types.
Try the same connector and driver with an earlier Power BI Desktop build. If it works there but fails in 2.155.385.0, that would strongly suggest a regression.I don’t see a documented SqlCapabilities or SQLGetInfo option that directly overrides per-column searchability for Sort or Group. SqlCapabilities mainly controls broader SQL grammar and supported operations; it may not replace the type-level searchability value used internally by the folding engine.
Since the trace shows the corrected SQLGetTypeInfo result before the warning, this may be an internal metadata-cache issue or a regression in the current Power BI Desktop ODBC folding implementation. I would recommend creating a minimal reproducible connector and reporting it through Microsoft support or the Power Query SDK repository, including:
The .mez connector
A minimal driver or trace
The failing M query
The full OdbcQuery/FoldingWarning
Results from both the current and an earlier Desktop versionIt would also be useful to know whether a simple equality filter folds successfully and only Table.Sort/Table.Group fail, or whether all predicates are rejected. That distinction could show whether Power BI is treating the column as unsearchable globally or only within specific folding operators.
