Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I need to access an API which needs to retrieve an access token with a call to an auth/token API call. I am using the following code to generate a Token and retrieve all records in a loop by making multiple calls to the API.
let
// Configuration constants
ClientId = #"CLIENT_ID",
ClientSecret = #"CLIENT_SECRET",
ServerUrl = "https://api.example.com",
JwtToken = #"JWT",
// Function for handling HTTP POST requests
HttpPost = (url as text, headers as record, body as record) as record =>
let
response = Json.Document(Web.Contents(url, [
Headers = headers,
Content = Text.ToBinary(Uri.BuildQueryString(body))
]))
in
response,
// Generate access token using JWT
GenerateAccessToken = () =>
let
url = ServerUrl & "path/to/oauth/token",
headers = [
#"Authorization" = "Basic " & Binary.ToText(Text.ToBinary(ClientId & ":" & ClientSecret), BinaryEncoding.Base64),
#"Content-Type" = "application/x-www-form-urlencoded"
],
body = [
grant_type = "urn:ietf:params:oauth:grant-type:jwt-bearer",
assertion = JwtToken
],
response = HttpPost(url, headers, body)
in
response[access_token],
// Retrieve the access token
AccessToken = GenerateAccessToken(),
// Recursive function to handle paginated API calls
FetchAllResults = (baseUrl as text, relativePath as text, queryParams as record, accumulatedResults as list) as list =>
let
// Add the access token to the query parameters
queryParamsWithToken = Record.AddField(queryParams, "access_token", AccessToken),
response = Json.Document(Web.Contents(baseUrl, [
RelativePath = relativePath,
Query = queryParamsWithToken
])),
records = response[records],
accumulatedResultsUpdated = List.Combine({accumulatedResults, records}),
nextPage = try response[navigation][nextPage][uri] otherwise null,
nextQueryParams =
if nextPage <> null then
Uri.Parts(nextPage)[Query]
else
null,
finalResults =
if nextQueryParams = null then
accumulatedResultsUpdated
else
@FetchAllResults(baseUrl, relativePath, nextQueryParams, accumulatedResultsUpdated)
in
finalResults,
// Initial query parameters
QueryParams = [
dateFrom = "2024-01-01T00:00:00.000Z",
dateTo = "2024-12-19T23:59:59.009Z",
view = "Simple",
perPage = "1000", // String value
page = "1" // String value
],
RelativePath = "path/to/api", // Extracted relative path
// Fetch all records
AllResults = FetchAllResults(ServerUrl, RelativePath, QueryParams, {}),
// Convert results to a table
ResultsTable = Table.FromList(AllResults, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
ResultsTable
Trying to run the above code result in the following error:
Expression.Error: The 'Authorization' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, Content-Type, If-Modified-Since, Prefer, Range, Referer
I am unable to set the Data Source Settings to Anonymous for this Query as it is apparently not allowed in Power BI. Any ideas on how to make this work would be great.
Hi @SalJav - Power BI allows passing authentication credentials via Web.Contents options rather than adding them manually in the Headers. You need to configure the query to use the Authorization header implicitly through the settings.
sharing the mcode in json format, please check and hope it works.
Proud to be a Super User! | |
Thank you so much for taking time to reply to my query. Unfortunately, I had already tried that with no success. But, I tried your solution again just to make sure and I get the following error:
DataSource.Error: Web.Contents with the Content option is only supported when connecting anonymously.
Details:
DataSourceKind=Web
DataSourcePath=path/to/oauth/token
and also I am not sure if creds in parameters will work for this API, just wanted to give it a run, which is not happening.
Update:
After Running the attached code, I could see the URL in Data Source Settings and set it to Anonymous. And, then tried to run the query again, this time I get a 400 Bad Request Error, which is returned by the API, and which means, it will not accept credentials in Parameters.
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |