Power Query Editor not passed filters to custom connector

[MukeshKudi]

I am writing a custom connector support a rest api. It works till getting the tables and respective data, but didn't pass filter paramaters from power query editor to custom connector to further passed to the api to filter data on source.

Please find below sample code.

// This file contains your Data Connector logic
section MyConnector;

// Auth0 variables
client_id = "***************************************";
redirect_uri = "https://oauth.powerbi.com/views/oauthredirect.html";
authorize_uri = "https://login.microsoftonline.com/common/oauth2/v2.0/authorize";
token_uri = "https://login.microsoftonline.com/common/oauth2/v2.0/token";
windowWidth = 1200;
windowHeight = 1000;
client_secret="***************************************";

// My API variables
vUrl = "https://***************************************/api/";

[DataSource.Kind="MyConnector", Publish="MyConnector.Publish"]
shared MyConnector.Contents = Value.ReplaceType(MyConnectorImpl, MyConnectorType);

MyConnectorType = type function (
    EMail as (type text meta [
        Documentation.FieldCaption = Extension.LoadString("EMailLabel"),
        Documentation.FieldDescription = Extension.LoadString("EMailDescription"),
        Documentation.SampleValues = { "32", "1" }
    ]))
    as table meta [
        Documentation.Name = Extension.LoadString("ApplicationName"),
        Documentation.LongDescription = Extension.LoadString("ApplicationDescription")
    ];

GetTableData = (tableName as text) as table =>
    let
        GetData = (optional filters as nullable record) as table =>
            let
                _= Diagnostics.Trace("MyConnector","GetTableData",TraceLevel.Information,[Message = "GetTableData called with filters", Data = filters]),
                filterQuery = if filters <> null then Uri.BuildQueryString(filters) else "",
                filterQuery1 = Text.FromBinary(Json.FromValue(filters)),
                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,

        GetRows = (optional options as nullable record) as table =>
            let
                _= Diagnostics.Trace("MyConnector","GetRows",TraceLevel.Information,[Message = "GetRows called with filters", Data = options]),
                 filterRows = if options <> null and Record.HasFields(options, "filter") then
                    options[Filter]
                else null,

                filterList = if filterRows <> null then Record.ToList(filterRows) else {},
                filterRecord = Record.FromList(List.Transform(filterList, each _{1}), List.Transform(filterList, each _{0})),
                


                data = GetData(filterRecord)
            in
                data,

        GetSchema = () as type =>
            let
                preview = GetData(null),
                tableType = Value.Type(preview)
            in
                tableType

    in
        Table.View(null, [
            GetType  = GetSchema,
            GetRows = GetRows,
            OnSelectRows = (rowspec) => GetRows([options = rowspec])
        ]);


GetTableNames = () as table =>
    let
        url = vUrl & "GetAccessResources",
        response = Web.Contents(url),
        json = Json.Document(response),
        tableCol = Table.FromRecords(json)
    in
        tableCol;

NavigationTable.Simple = (dummyParam as text) =>
    let
        tableNames = GetTableNames(),
        navTable = Table.AddColumn(tableNames, "Data",each GetTableData([TableName])),  //each GetTableData([TableName])
         withKey = Table.AddColumn(navTable, "Key", each [TableName]),
        withKind = Table.AddColumn(withKey, "ItemKind", each "Table"),
        withItemName = Table.AddColumn(withKind, "ItemName", each "Table"),
        withIsLeaf = Table.AddColumn(withItemName, "IsLeaf", each true),
        renamed = Table.RenameColumns(withIsLeaf, {{"TableName", "Name"}}),
        navigation = Table.ToNavigationTable(renamed, {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
    in
        navigation;

MyConnectorImpl = (Email as text) =>
    let
        NavTable = NavigationTable.Simple(Email)
    in
        NavTable;

// Data Source Kind description
MyConnector = [
    Authentication = [
        OAuth =  [
            StartLogin=StartLogin,
            FinishLogin=FinishLogin,
            Refresh=Refresh,
            Logout=Logout
        ]
    ],
    Label = Extension.LoadString("DataSourceLabel")
];

// Data Source UI publishing description
MyConnector.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
    LearnMoreUrl = "https://app.myapp.se",
    SourceImage = MyConnector.Icons,
    SourceTypeImage = MyConnector.Icons
];

// StartLogin helper method
StartLogin = (resourceUrl, state, display) =>
    let
        AuthorizeUrl = authorize_uri & "?" & Uri.BuildQueryString(
        [
            client_id = client_id,
            scope = "api://8a145dd6-0f61-420c-80f8-4cfffd97f4cc/User.Read",
            response_type = "code",
            state = state,
            redirect_uri = redirect_uri
        ])
    in
        [
            LoginUri = AuthorizeUrl,
            CallbackUri = redirect_uri,
            WindowHeight = windowHeight,
            WindowWidth = windowWidth,
            Context = []
        ];

// Refresh method
Refresh = (resourceUrl, refresh_token) =>
    let
         result =  TokenMethod("refresh_token", "refresh_token", refresh_token)
    in
        result;

// FinishLogin Method
FinishLogin = (context, callbackUri, state) =>
    let
        parts = Uri.Parts(callbackUri)[Query],
        // if the query string contains an "error" field, raise an error
        // otherwise call TokenMethod to exchange our code for an access_token
        result = if (Record.HasFields(parts, {"error", "error_description"})) then 
                    error Error.Record(parts[error], parts[error_description], parts)
                 else
                    TokenMethod("authorization_code", "code", parts[code])
    in
        result;

// Token function.
TokenMethod = (grantType, tokenField, code) =>
    let
        queryString = [
            grant_type = grantType,
            redirect_uri = redirect_uri,
            client_id = client_id,
            client_secret = client_secret
        ],
        queryWithCode = Record.AddField(queryString, tokenField, code),

        tokenResponse = Web.Contents(token_uri, [
            Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
            Headers = [
                #"Content-type" = "application/x-www-form-urlencoded",
                #"Accept" = "application/json"
            ],
            ManualStatusHandling = {400} 
        ]),
        body = Json.Document(tokenResponse),
        result = if (Record.HasFields(body, {"error", "error_description"})) then 
                    error Error.Record(body[error], body[error_description], body)
                 else
                    body
    in
        result;

Logout = (token) => null;

MyConnector.Icons = [
    Icon16 = { Extension.Contents("MyApp_symbol_16x16.png"), Extension.Contents("MyApp_symbol_20x20.png"), Extension.Contents("MyApp_symbol_24x24.png"), Extension.Contents("MyApp_symbol_32x32.png") },
    Icon32 = { Extension.Contents("MyApp_symbol_32x32.png"), Extension.Contents("MyApp_symbol_40x40.png"), Extension.Contents("MyApp_symbol_48x48.png"), Extension.Contents("MyApp_symbol_64x64.png") }
];

Table.ToNavigationTable = (
    table as table,
    keyColumns as list,
    nameColumn as text,
    dataColumn as text,
    itemKindColumn as text,
    itemNameColumn as text,
    isLeafColumn as text
) as table =>
    let
        tableType = Value.Type(table),
        newTableType = Type.AddTableKey(tableType, keyColumns, true) meta 
        [
            NavigationTable.NameColumn = nameColumn, 
            NavigationTable.DataColumn = dataColumn,
            NavigationTable.ItemKindColumn = itemKindColumn, 
            Preview.DelayColumn = itemNameColumn, 
            NavigationTable.IsLeafColumn = isLeafColumn
        ],
        navigationTable = Value.ReplaceType(table, newTableType)
    in
        navigationTable;

[v-sgandrathi]

HI @MukeshKudi,

Thank you @Nasif_Azam for your response.

Has your issue been resolved?If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.

Thank you for your understanding!

[v-sgandrathi]

Hi @MukeshKudi,

We wanted to follow up to see if our suggestion was helpful. Please let us know how things are progressing and if you are still encountering any issues.

If the response resolved your problem, you may mark it as the solution and give it a thumbs up to assist others in the community as well.

Thank you.

[Nasif_Azam]

Hey @MukeshKudi ,

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

[MukeshKudi]

Thanks Sahasra.

Can you please provide sample for both solutions.

This will be great help, Thanks.

[v-sgandrathi]

Hi @MukeshKudi,

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.