Unexpected Multiple Requests from Power BI Connector

[Scheißy]

Hey guys,

I'm new to this Power BI game, so please be gentle with me. 🙂

I'm trying to build a custom connector that downloads three CSV files from an API endpoint and imports the data into Power BI. This part is working so far, but while testing the connector and reviewing the log files, I noticed that my API authorization endpoint is being hit multiple times:

• Once while loading the navigation table

• And once for each CSV file during data import

That adds up to 4 auth calls, which seems excessive - especially considering I might need to add more CSV files to the import.

But my concerns don’t stop there. After checking the auth endpoints, I decided to inspect the file endpoints as well—and what I found is even more worrying: each file endpoint is requested at least 3, and sometimes up to 5 times. I definitely didn’t expect that many requests, and with more files planned, things could get even worse.

Here's what my code looks like so far:

section MyConnector;

[DataSource.Kind="MyConnector", Publish="MyConnector.Publish"]

shared MyConnector.Contents = () =>
    let
        auth = getAuthResponse(),
        token = auth[token],

        _token = if debug <> null then Diagnostics.Trace(TraceLevel.Information, "Token value: " & token, token) else token,

        profiles = "11b567b459b3d5475262643f2f8304ea51258863",
        effects = "116fc8ac5376b83b29277bf45400dad1bce22c5c",
        reports = "02a0a26611d91b33fa09d0a1f5d5b820896acee9",

        tables = {
            { "Profiles", if debug <> null then Diagnostics.Trace(TraceLevel.Information, "Loading profiles CSV...", loadCsvFile(profiles, _token)) else loadCsvFile(profiles, _token) },
            { "Effects", if debug <> null then Diagnostics.Trace(TraceLevel.Information, "Loading effects CSV...", loadCsvFile(effects, _token)) else loadCsvFile(effects,  _token) },
            { "Status Reports", if debug <> null then Diagnostics.Trace(TraceLevel.Information, "Loading reports CSV...", loadCsvFile(reports, _token)) else loadCsvFile(reports, _token) }
        },

        source = #table({ "Name", "Data" }, tables),
        navTable = Table.ToNavigationTable(source, { "Name" }, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
    in
        navTable;

shared getAuthResponse = let
    authFunc = () =>
        let
            credentials = Extension.CurrentCredential(),
            authUrl = MyConnector.BaseUrl,

            response = Web.Contents(authUrl, [
                RelativePath = "/v1/auth/",
                Headers = [
                    #"Content-Type" = "application/json"
                ],
                Content = Json.FromValue( [user = credentials[Username], token = credentials[Password] ])
            ]),

            json = Json.Document(response),
            authResponseData = json[data],
            _authResponseData = if debug <> null then Diagnostics.Trace(TraceLevel.Information, "auth response data: " & Text.FromBinary(Json.FromValue(authResponseData)), authResponseData) else authResponseData
        in
            _authResponseData
in
    authFunc;

loadCsvFile = (relativePath as text, token as text) =>
    let
        _token = if debug <> null then Diagnostics.Trace(TraceLevel.Information, "loadCsvFile " & relativePath & " called with token: " & token, token) else token,

        // resourcePath = { MyConnector.BaseUrl },
        response = Web.Contents(MyConnector.StorageUrl, [
            RelativePath = relativePath,
            // ResourcePath = resourcePath,
            Headers = [
                Authorization = "Bearer " & _token
            ]
        ]),
        csv = Csv.Document(response, [
            Delimiter = ",",
            Encoding = 65001,
            QuoteStyle = QuoteStyle.Csv
        ]),
        table = Table.PromoteHeaders(csv)
    in
        table;

As you might be able to tell, I’ve already tried to implement some caching, but without much success. I also added some debug output, and the results are concerning - for example, the message "auth response data:" appears up to 28 times in the Power BI logs. So I might actually be lucky that the connector is hitting the authorization endpoint only 4 times ...

Thank you very much for any input or ideas how to reduce the number of request - or an explanation why there are so many requets.

[v-kpoloju-msft]

Hi @Scheißy,

Thank you for reaching out to the Microsoft fabric community forum. Also, thanks to @Nasif_Azam, for those inputs on this thread and for the detailed description you are absolutely on the right track, and great job using diagnostics for deeper insight.

The issue you are experiencing, where the API endpoints (especially authentication and file download) are hit multiple times, is a known behaviour in custom Power BI connectors. It often happens because Power BI evaluates the connector logic multiple times for example, during schema discovery, preview, and data load phases.

To reduce redundant calls, especially for authorization and file download, you can implement caching using Function.Cached in your Power Query M code. This ensures that your token and file data are computed once and reused across calls.

Cache the authentication token using Function.Cached. Wrap your file download function (loadCsvFile) in another Function.Cached to prevent redundant fetches. Consider using ResourcePath in Web.Contents to improve connector performance.

Kindly refer to the below mentioned links for more information:
Web.Contents - Power Query M | Microsoft Learn
Power Query M function reference - Power Query M | Microsoft Learn
Connector extensibility in Power BI - Power BI | Microsoft Learn

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 Fabric Community Forum.

[Scheißy]

Hey @v-kpoloju-msft

I tried to look into your suggestions:
First of all I rechecked the "ResourcePath" option. I switched from "Visual Studio 2019" to "Visual Studio Code" and I did not get the error message that "ResourcePath" is an invalid option. I was hopeful, and since I also updated my Power BI to the newest version I was really hoping that I'm making some progress here. But when I tried the new connector in the updated Power BI version I was hitting the same result: "ResourcePath" is an invalid option.

Checking the Web.Contents link you posted above I saw that "ResourcePath" isn't mentioned there as a possible option. In the error response I got in Power BI there are listed possible options for "Web.Contents". Of course no "ResourcePath" was mentioned there, but I found a "Resource" parameter (which is also not listed in the documentation). So I thought maybe the option name just changed. I tried "Resource" instead of "ResourcePath" and my connector worked again in Power BI - but unfortunately the same number of requests are shown in the log 😕
Next thing I tried to wrap the download function. I checked the links again, but couldn't find any examples or help how to do so. But with a little external help I came up with something like this:

loadCsvFile = (relativePath as text, token as text) =>
    let cachedLoadCsvFile = () =>
        let
            _token = if debug <> null then Diagnostics.Trace(TraceLevel.Information, "loadCsvFile " & relativePath & " called with token: " & token, token) else token,

            response = Web.Contents(MyConnector.StorageUrl, [
                RelativePath = relativePath,
                Headers = [
                    Authorization = "Bearer " & _token
                ]
            ]),
            csv = Csv.Document(response, [
                Delimiter = ",",
                Encoding = 65001,
                QuoteStyle = QuoteStyle.Csv
            ]),
            table = Table.PromoteHeaders(csv)
        in
            table
    in
        cachedLoadCsvFile;

This actually helped a bit - all the download requests which are made during the navigation table is loaded are gone. This reduces the number of file requests from 8 for every single file to 6. The only thing that unsettles me a bit is that in the navigator the files are now displayed as "functions" and not as "tables" anymore (just to be clear: I'm only talking about the icon here). But I couldn't see any difference in the actual data which was imported, so I hope this is fine.
Of course I would love to reduce the number of requests more than that. So feel free to add more suggestions 🙂

Regards,

Scheißy

[v-kpoloju-msft]

Hi @Scheißy,

Thank you for your detailed follow-up and for sharing your findings really appreciate the structured troubleshooting you have done here.

You are right in noticing that ResourcePath is not a documented parameter for Web.Contents. It was previously used in some internal/custom connector implementations but is not officially supported in the current Web.Contents - Power Query M | Microsoft Learn

Instead, RelativePath and Query are the recommended and documented options, as outlined here: Web.Contents - PowerQuery M | Microsoft Learn

Using loadCsvFile with a wrapped function is a smart move, and you are correct that this can help with reducing redundant requests during query folding and preview generation, especially in custom connectors.

The change in icon from "table" to "function" in the navigator is expected when using a function wrapper, it should not affect data load, as you have rightly observed. As long as the result loads data correctly and consistently, you are on the right track.

Although experimental, you might try the Diagnostics.Cache function to further limit backend requests, especially during navigation. Monitor using diagnostic settings - Azure Cache for Redis | Microsoft Learn

Use Api.Folder (if applicable): If you are calling APIs with many similar file endpoints, you can sometimes bundle them more efficiently using folder-like behaviours. Disable Preview in   Navigator (for large APIs): While not a direct solution in M code, instructing users to disable “Allow data preview to download in the background” in Power BI options can reduce load-time requests.

As you have already eliminated 2 requests per file, that is real progress. The function icon behaviour is expected and harmless. If you are still facing request overhead, it may be inherent to how Power Query navigators parse folder/endpoint structures for previews.

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 Fabric Community Forum.

[v-kpoloju-msft]

Hi @Scheißy.,

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.

[Scheißy]

Hey @v-kpoloju-msft,

priorities shift at work this week, so I didn't have time yet to look into your suggestions. Hope I will find some time next week to look into Diagnostics.Cache.

Api.Folder doesn't look poosible at this point, but maybe later.

Disabling the preview didn't change anything for me regarding the number of requests. But since I already reduced requests at that point (and I don't have controll over everyone who uses the connector to turn that option off) I don't think that's helping.

Sorry again for the delay and thanks again for all the help and input!

Regards,

Scheißy

[v-kpoloju-msft]

Hi @Scheißy,

Thanks for getting back to us and for the update. No problem at all we understand priorities can shift. Whenever you get the chance to test with Diagnostics.Cache or revisit the Api.Folder option, feel free to share your findings here so we can continue from there.

Thanks again for your time and for letting us know.

[v-kpoloju-msft]

Hi @Scheißy,

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-msft]

Hi @Scheißy,

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.

[Scheißy]

Hey @v-kpoloju-msft,

thanks for your input. I'll try that stuff and check your links, but I already have a question:

You mention the "ResourcePath" option. I tried that, but I always got an error message that the ResourcePath option isn't a valid Web.Contents option. Google said that this error message should only happen in Visual Studio, but I keep hitting that same error message in Power BI. What am I doing wrong?

[Nasif_Azam]

Hey  @Scheißy ,

It seems that the authorization request is being called multiple times because it's inside the MyConnector.Contents function, which gets triggered for each CSV file. To reduce the number of requests:

1. Make the authorization request once and store the token globally (outside the function). Pass this token to all the CSV loads instead of requesting it repeatedly.

   Example:

   shared token = getAuthResponse()[token]

2. Ensure Web.Contents is not called multiple times unintentionally. You can optimize this by making sure the CSV files are loaded only once per request.

3. Since your logs show repeated requests, make sure the file loading is not triggering additional unnecessary requests. Review your debug outputs and check if it's causing multiple calls.

4. If your token expires, set up a rule to refresh it only when needed, not on every request.

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

[Scheißy]

Hey @Nasif_Azam!

thank you for your input. I tried your suggestion, but as soon as I try to assign the getAuthResponse result to a shared variable I run into the exception "Microsoft.Mashup.Engine.Interface.UnpermittedResourceAccessException".

PS: it works when I remove the "shared" part for the token variable. But then I can't see any differences in the number of requests.

[Nasif_Azam]

Hey  @Scheißy ,

You are absolutely right Power Query’s engine does not allow Web.Contents to run in shared/global scope, which is why you saw the UnpermittedResourceAccessException. You can try:

1. Use Lazy Evaluation + Let-Caching Inside MyConnector.Contents

Move the getAuthResponse() call once insideMyConnector.Contents, and reuse thetoken within the scope of the same function using a let block:

shared MyConnector.Contents = () =>
    let
        auth = getAuthResponse(),
        token = auth[token],

        profilesData = loadCsvFile("11b567...", token),
        effectsData  = loadCsvFile("116fc8...", token),
        reportsData  = loadCsvFile("02a0a2...", token),

        source = #table({ "Name", "Data" }, {
            { "Profiles", profilesData },
            { "Effects", effectsData },
            { "Status Reports", reportsData }
        }),
        navTable = Table.ToNavigationTable(source, { "Name" }, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
    in
        navTable;

 

2. Avoid Nested Diagnostics.Trace Causing Multiple Evaluations

In Power Query, anything inside Diagnostics.Trace may get evaluated more than once (once for value, once for metadata). You can reduce this by moving trace logging after evaluation, or removing trace for heavy operations like getAuthResponse() and loadCsvFile() during testing.

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

[Scheißy]

Hey @Nasif_Azam,

thanks again for your suggestions and for the time you take to help me. I really appreciate that 🙂

1. That looks pretty much like the code version I started with. I added all the debugging and loading stuff in trying to reduce the number of requests. When I "go back" to your suggestion I still can see the 4 auth request in my logs.

2. I removed all the debug stuff (not just "turned it off") to see if my Diagontics stuff is doing any harm. Unfortunately I'm still getting 4 auth requests and 24 file requests (8 for each of the 3 files) in my server logs.

Regards,

Scheißy