Query Folding is not enabled for custom connector with source as API

[MukeshKudi]

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. 

[v-kpoloju-msft]

Hi @MukeshKudi,
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.

1. Replace null in Table.View with a valid source object like [Name = "MyCustomAPI", Kind = "MyCustomConnector"].
2. Update Web.Contents to use RelativePath and Query instead of concatenated URLs.
3. 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.