Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
I'm developing a new custom data connector for Power BI based on a new ODBC driver.
I'm trying to fully support DirectQuery mode as well as the ODBC driver will allow.
However, receiving some folding errors when attempting simple functionality like scrolling past the first 501 records and trying to filter a column based on an existing value.
In both these cases, the following error occurs.
The question is how can we diagnose the underlying missing capability or property which is causing the folding error to occur.
With diagnostic tracing enabled the following errors are logged:
DataMashup.Trace Warning: 24579 : {"Start":"2023-01-16T18:27:19.3263058Z","Action":"RemotePageReader/RunStub","HostProcessId":"29440","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine1.Runtime.ValueException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression.\r\nStackTrace:\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQueryDomain.ReportFoldingFailure(NotSupportedException ex)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQuery.SelectRows(FunctionValue function)\r\n at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query, Func`2 operation)\r\n at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query)\r\n at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query, Func`2 operation)\r\n at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query)\r\n at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query, Func`2 operation)\r\n at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query)\r\n at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query, Func`2 operation)\r\n at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.VisitQuery(Query query)\r\n at Microsoft.Mashup.Engine1.Runtime.OptimizingQueryVisitor.Optimize(Query query)\r\n at Microsoft.Mashup.Engine1.Language.Query.QueryTableValue.get_OptimizedQuery()\r\n at Microsoft.Mashup.Engine1.Language.Query.QueryTableValue.GetReader()\r\n at Microsoft.Mashup.Engine.Interface.Tracing.TracingDataReaderSource.get_PageReader()\r\n at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass10_1.<OnBeginGetDataReaderSource>b__1()\r\n at Microsoft.Mashup.Evaluator.RemotePageReader.<>c__DisplayClass1_1.<RunStub>b__0()\r\n at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(String entryName, IEngineHost engineHost, IMessageChannel channel, Action action)\r\n\r\nDetail: null\r\n\r\n","ProductVersion":"2.112.1161.0 (22.12)","ActivityId":"5397852b-7e33-48a5-be50-550c61c8a3f7","Process":"Microsoft.Mashup.Container.NetFX45","Pid":28900,"Tid":1,"Duration":"00:00:00.0001674"}
Here is the essential contents of the custom connector file.
/* This is the method for connection to ODBC*/
[DataSource.Kind="OdbcBasedConnector", Publish="OdbcBasedConnector.UI"]
shared OdbcBasedConnector.Contents = (dsn as text) as table =>
let
//
// Connection string settings
//
ConnectionString = [
DSN=dsn
],
//
// Handle credentials
// Credentials are not persisted with the query and are set through a separate
// record field - CredentialConnectionString. The base Odbc.DataSource function
// will handle UsernamePassword authentication automatically, but it is explictly
// handled here as an example.
//
Credential = Extension.CurrentCredential(),
encryptionEnabled = Credential[EncryptConnection]? = true,
CredentialConnectionString = [
UID = Credential[Username],
PWD = Credential[Password]
],
MySqlCapabilities = Diagnostics.LogValue("SqlCapabilities_Options", [
GroupByCapabilities = ODBC[SQL_GB][SQL_GB_GROUP_BY_CONTAINS_SELECT],
SupportsNumericLiterals = true,
SupportsStringLiterals = true,
SupportsOdbcDateLiterals = true,
SupportsOdbcTimeLiterals = true,
SupportsOdbcTimestampLiterals = true,
PrepareStatements = false,
StringLiteralEscapeCharacters = { "\" },
SupportsTop = false,
LimitClauseKind = LimitClauseKind.LimitOffset,
FractionalSecondsScale = 3
]),
MySQLGetInfo = Diagnostics.LogValue("SQLGetInfo_Options", [
SQL_SQL92_PREDICATES = ODBC[SQL_SP][All], // (SQL_SP) all
SQL_AGGREGATE_FUNCTIONS = ODBC[SQL_AF][All], //all
SQL_SQL_CONFORMANCE = ODBC[SQL_SC][SQL_SC_SQL92_FULL], // FULL
SQL_GROUP_BY = ODBC[SQL_GB][SQL_GB_GROUP_BY_CONTAINS_SELECT]
]),
MySQLGetFunctions = Diagnostics.LogValue("SQLGetFunctions_Options", [
SQL_API_SQLBINDPARAMETER = false,
SQL_CONVERT_FUNCTIONS = ODBC[SQL_FN_CVT][SQL_FN_CVT_CAST] // CAST
]),
// Build AstVisitor
// This record allows you to customize the generated SQL for certain
// operations. The most common usage is to define syntax for LIMIT/OFFSET operators
// when TOP is not supported.
//
MyAstVisitor = [
LimitClause = (skip, take) =>
let
offset = if (skip <> null and skip > 0) then Text.Format("OFFSET #{0} ROWS", {skip}) else "",
limit = if (take <> null) then Text.Format("LIMIT #{0}", {take}) else ""
in
[
Text = Text.Format("#{0} #{1}", {limit, offset}),
Location = "AfterQuerySpecification"
]
],
//
// Call to Odbc.DataSource
//
OdbcDatasource = Odbc.DataSource(ConnectionString, [
// Enables client side connection pooling for the ODBC driver.
// Most drivers will want to set this value to true.
ClientConnectionPooling = true,
// When HierarchialNavigation is set to true, the navigation tree
// will be organized by Database -> Schema -> Table. When set to false,
// all tables will be displayed in a flat list using fully qualified names.
HierarchicalNavigation = true,
//Expose Native Query
HideNativeQuery = false,
//Allows the M engine to select a compatible data type when conversion between two specific numeric types is not declared as supported in the SQL_CONVERT_* capabilities.
SoftNumbers = true,
TolerateConcatOverflow = true,
// These values should be set by previous steps
CredentialConnectionString = CredentialConnectionString,
//Requires AstVisitor since Top is not supported in DocumentDB
AstVisitor = MyAstVisitor,
SqlCapabilities = MySqlCapabilities,
SQLGetInfo = MySQLGetInfo,
SQLGetFunctions = MySQLGetFunctions
])
in OdbcDatasource;<div> </div>
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.