Hi,
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?
Thanks!
(BranchCode as number, AccountNumber as number, StartDate as text, EndDate as text) =>
let
Extratos =
let
TokenUsado = fxGetToken(Text.From(Number.Random()))
in
try
Json.Document(
Web.Contents(
"https://xxxxx.com.br",
[
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())
]
otherwise
null
in
Extratos