Power Query Editor not passed filters to custom connector

Hi Anonymous,

Thank you for sharing your detailed explanation and the sample code, it really helps in understanding the issue better.

From your description, your custom connector successfully fetches tables and data from the REST API. However, the filters applied in Power Query Editor are not being passed to the API call. This is a common issue when building custom connectors and relates to how Power Query handles filters internally.

By default, Power Query does not automatically pass UI-applied filters to your connector unless it's explicitly designed to interpret them. Even though you’ve added OnSelectRows = (rowspec) => GetRows([options = rowspec]), the filters from Power Query won't reach this function unless your connector supports query folding, a mechanism that allows Power BI to push transformations like filtering or sorting to the data source.

To address this, consider the following approaches:

Explicit Parameter Passing: Modify your connector to accept filters as explicit parameters (e.g., in MyConnectorImpl or GetTableData). Users can define filter values directly within the function, passing them along to your API request with full control.
Implement Query Folding Logic: To keep the Power Query UI experience intact, write folding logic using the Table.View construct. This involves inspecting the rowspec or options record inside GetRows to capture filter conditions and convert them into query parameters that your API understands. It’s more advanced but offers a smoother experience for end users.

Happy to help! If this addressed your concern, marking it as "Accepted Solution" and giving us "kudos" would be valuable for others in the community.

Regards,

Sahasra

Comunity Support Team.

Hey Anonymous ,

The issue you're facing seems to be that the filter parameters from Power Query Editor aren't being passed properly to the custom connector, which in turn prevents them from being forwarded to the API for filtering.

1. Reviewing the Filter Logic:
   The code seems to construct the filterQuery based on the input parameters (filters), but the part of the code where the query string is constructed might not be functioning as expected. You might want to log the values of filters and filterQuery to ensure they contain the data you expect before passing them to the Web.Contents function.

   Solution:

   • Double-check if the filters are being passed correctly. You can use the Diagnostics.Trace method to log the value of the filters variable and ensure they are getting passed as expected.

   • You may want to inspect the filter construction logic carefully:

     filterQuery = if filters <> null then Uri.BuildQueryString(filters) else ""

     This might not always create the correct query string, especially if filters contains nested or complex values. You may want to try serializing the filters to a string format manually using Text.FromBinary(Json.FromValue(filters)) as it looks like it’s already being done in filterQuery1, but isn't used in your url construction.

2. Ensure Filter Format Matches API Expectations:
   You need to ensure that the API you're interacting with accepts the filters in the correct format. If filterQuery is not constructed correctly, the API won't receive the filter parameters.

   Solution:

   • Ensure that the filter format aligns with what the API expects. For example, check whether the filters should be sent as a query string, a JSON payload, or any other specific format.

   • If Uri.BuildQueryString(filters) doesn’t work as intended due to the structure of filters, try encoding the filters differently or using a different method to pass them.

3. Test the API Manually:
   As a troubleshooting step, you could manually test the API with the same filter parameters using tools like Postman. This will confirm whether the API behaves as expected when filters are provided in the same way as your connector.

4. Consider Enhancements to the Connector:
   Depending on your situation, consider adding further flexibility to your custom connector, such as allowing for more sophisticated filter handling or providing clear error messages if filters aren’t passed correctly.

Here’s an updated example with enhanced logging:

let
    _ = Diagnostics.Trace("MyConnector", "GetTableData", TraceLevel.Information, [Message = "Filters passed", Data = filters]),
    filterQuery = if filters <> null then Uri.BuildQueryString(filters) else "",
    url = vUrl & "GetTableData?name=" & Uri.EscapeDataString(tableName) & (if filterQuery <> "" then "&" & filterQuery else ""),
    response = Web.Contents(url),
    json = Json.Document(response),
    asTable = Table.FromRecords(json)
in
    asTable

For Detailed Information:

Power Query Documentation
M Functions Reference
Uri.BuildQueryString Documentation
Diagnostics.Trace Documentation
REST API Filter Guidelines

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam