Forum Discussion
Query Folding is not enabled for custom connector with source as API
I am following the query folding apparoch and having below version of TableView.
GetTableView = (tableName as text) as table =>
let
schemaPreview = GetTableData(tableName, [PageNumber = 1, PageSize = 100]),
schemaType = Value.Type(schemaPreview)
in
Table.View(
null,
[
GetRows = (optional options as nullable record) =>
let
rowFilters = if Record.HasFields(options, "RowFilters") then options[RowFilters] else {},
filterList = List.Transform(rowFilters, each [
Column = _[ColumnName],
Operator = _[Kind],
Value = _[Value]
]),
// Build OData-style filter string
filterExpressions = List.Transform(filterList, each
_[Column] & " " &
(if _[Operator] = "GreaterThan" then ">"
else if _[Operator] = "GreaterThanOrEqual" then ">="
else if _[Operator] = "LessThan" then "<"
else if _[Operator] = "LessThanOrEqual" then "<="
else if _[Operator] = "Equal" then "="
else if _[Operator] = "NotEqual" then "<>"
else error "Unsupported operator" & _[Operator])
& " " &
"'" & DateTime.ToText(_[Value], "yyyy-MM-ddTHH:mm:ss") & "'"
),
// Join filters with "and"
filterQueryString = if List.Count(filterExpressions) > 0 then
"$filter=" & Text.Combine(filterExpressions, " and ")
else
"",
fullUrl = dUrl & "?" & filterQueryString & "&$top=150&$skip=0",
response = Web.Contents(fullUrl),
// Read table filters from Power Query folding options
// Optionally, include PageNumber/PageSize if we use pagination
//apiFilters = if filterRecord <> null then Record.AddField(filterRecord, "PageNumber", 1) else [PageNumber = 1],
result = GetTableData(tableName, filterQueryString)
in
result,
GetSchema = () => schemaType,
GetType = () => schemaType
]
);
On Power BI, Below is the M query, I am using to get the filtered data on Transaction_Date_UTC.
let Source = MyConnector.Contents(null), table1 = Table.SelectRows(Source, each DateTime.From([Transaction_Date_UTC]) > #datetime(2023, 1, 1, 0, 0, 0) and DateTime.From([Transaction_Date_UTC]) <= #datetime(2023, 12, 31, 23, 59, 59)) in table1
still Custom Connector not able to recevie rowfilters.
Hi Anonymous,
Thank you for posting your query in Microsoft Fabric Community Forum.Based on your implementation, the root cause is that query folding is not being triggered because Table.View has null as its first argument. For query folding to work, this should be a valid data source reference, not null.
Additionally, your Web.Contents call should use the RelativePath and Query fields for Power BI to recognize the call as foldable. I suggest the following steps which are useful to you.
- Replace null in Table.View with a valid source object like [Name = "MyCustomAPI", Kind = "MyCustomConnector"].
- Update Web.Contents to use RelativePath and Query instead of concatenated URLs.
- Ensure you handle options[RowFilters] properly and log it temporarily to check if folding is happening.
Also, here I provided some official Microsoft Documents for more information:
Table.View - PowerQuery M | Microsoft Learn
TripPin 10 - Basic Query Folding - Power Query | Microsoft Learn
Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn
OData comparison operator reference - Azure AI Search | Microsoft Learn
There is a detailed community discussion that covers these folding requirements in the context of Table.View, which you may find helpful:
Can we enable folding for custom query
Please check whether the schema is correctly typed and that your filter expression uses valid OData syntax. Also, make sure your API respects these query parameters.
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.
4 Replies
- v-kpoloju-msftCommunity Support
Hi Anonymous,
Thank you for posting your query in Microsoft Fabric Community Forum.Based on your implementation, the root cause is that query folding is not being triggered because Table.View has null as its first argument. For query folding to work, this should be a valid data source reference, not null.
Additionally, your Web.Contents call should use the RelativePath and Query fields for Power BI to recognize the call as foldable. I suggest the following steps which are useful to you.
- Replace null in Table.View with a valid source object like [Name = "MyCustomAPI", Kind = "MyCustomConnector"].
- Update Web.Contents to use RelativePath and Query instead of concatenated URLs.
- Ensure you handle options[RowFilters] properly and log it temporarily to check if folding is happening.
Also, here I provided some official Microsoft Documents for more information:
Table.View - PowerQuery M | Microsoft Learn
TripPin 10 - Basic Query Folding - Power Query | Microsoft Learn
Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn
OData comparison operator reference - Azure AI Search | Microsoft Learn
There is a detailed community discussion that covers these folding requirements in the context of Table.View, which you may find helpful:
Can we enable folding for custom query
Please check whether the schema is correctly typed and that your filter expression uses valid OData syntax. Also, make sure your API respects these query parameters.
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.- v-kpoloju-msftCommunity Support
Hi Anonymous,
Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.Thank you.
- v-kpoloju-msftCommunity Support
Hi Anonymous,
Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.Thank you.