How to retrieve Auth Token with JWT using REST API call in Power BI

[SalJav]

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.

[rajendraongole1]

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.

[SalJav]

Hi @rajendraongole1 

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.