Refresh API with dynamic root URL in Power BI online


My scenario is as follows and I ask for your help, please:

I need a query to make many calls to an API that returns bank statements. To do this, I used the function I pasted below in a dataflow (I omitted website and credentials for reasons of protecting private information).

However, the refresh is not completed in the Power BI service and it seems to me that the reason would be the following: Web.Contents requires a static url that already allows authentication. However, in my case, authentication only occurs with the url that includes a dynamic part with the number of a specific branch code and account. The root url does not exist and returns an error.

So, when I put the dynamic part in the RelativePath parameter, authentication does not occur.

On the other hand, when I use the root url with the dynamic part, Power BI returns the error that it is impossible to save the query because it includes dynamic data source.

Just to test that the rest of the code is correct, I ran the query with a fixed value for “BranchCode” and “AccountNumber” in the root url (i.e. without RelativePath) and the update worked in the Power BI service. My problem is that I have a large list with these parameters to query and I need them to be dynamic.

In Power BI desktop, everything works perfectly.

I would like to know if it is possible to make this connection to the API through Power Query online or if it is a limitation that cannot be overcome.

I’m already using the option to skip test connection and the anonymous authentication method.

Are there any workarounds to get around the requirements of Power BI online?


(BranchCode as number, AccountNumber as number, StartDate as text, EndDate as text) =>
    Extratos = 
        TokenUsado = fxGetToken(Text.From(Number.Random()))
                Headers = [
                  Accept          = "application/json", 
                  #"Content-Type" = "application/json", 
                  Authorization   = "Bearer " & TokenUsado
                RelativePath = "/statements/" & Text.From(BranchCode) & "-" & Text.From(AccountNumber), 
                Query = [
                   startDate = StartDate, 
                   endDate = EndDate, 
                   #"gw-dev-app-key" = "xxxxxxxx"
            & [
              Branch         = BranchCode, 
              Account           = AccountNumber, 
              DateAPIAccess = DateTime.Date(DateTime.LocalNow())
1 Like

Hi @LucianaBuchala,

I found the following article on Stack Overflow that seems to be about the same issue you are having.

The post does include a header with a token like yours. I think the main requirement is to already have a table, either in the model or being constructed in PowerQuery, that you can add the statement document to as a binary column by calling the custom function as described in the post.

I hope this helps!