Issues with dynamic token retrieval "query. DataSource.Error: Web.Contents failed to get contents "

[acc-int-ail]

I am trying to integrate an REST API source to power bi, the goal is to have it incrementally refresh, but the operations for the REST API uses dynamic access tokens. So I need to make a post function to get the access token, and link that function to the authorization in the Get request.

I have already posted a message about this Get-token-function-for-API-request-with-dynamic-access-token and thought I had the solution, and I have tried everything (it feels like, including the solution in the thread above). But still get this error

I am gonna paste all different functions I have tried and then I hope someone can give me a very detailed description on how to succeed, or what other possibilities I have  to import data (simply) from a custom API to power BI.

Img1 = connection to the api and fetching some data with an already generated Bearer token

Img1 = option1

Img2 = option2

Img3 = option3

Img4 = option4

first connecting to the API, it works, I have a table of some dataimg1img2img3img4

Please help me understand what I am doing wrong or suggest another way to import data with incremental refresh and automatic generate of access token.

[Daryl-Lynch-Bzy · Accepted solution]

Hi @acc-int-ail - you just need to get the syntax correct for Power BI to work, but it is difficult to follow with see what Postman is doing (with the sensitive information removed).  Here is an example using Power BI REST API with Service Principal:

##This is the structure of the HTTP REST API Call

POST https://login.windows.net/{#tenant id}/oauth2/token
Content-Type: application/x-www-form-urlencoded

grant_type=client_credentials&resource={#resource}&client_id={#client id}&client_secret={#client secret}

 Now look at the Power BI Version - This helpfully because you can see all the intermediate steps results before the APICall fails because there is no Client ID, Secret or Tenant.  Note this works because of the Content Type requirements defined in the header, and the structure for bodystring including grant_type is often used.

##This is the Power BI Query Script

let
    ClientID = "<clientid>",
    Secret = "<clientsecret>",
    TenantID = "<tenantid>",
    Resource = "<resource>",    
    
    BaseURL = "https://login.windows.net/" & TenantID & "/oauth2/token",

    BodyString = "grant_type=client_credentials&resource=" & Resource  & "&client_id=" & ClientID & "&client_secret=" & Secret,

    Body = Text.ToBinary(BodyString),

    APICall = Web.Contents(
                BaseURL,
                [
                    Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
                    Content = Body
                ]
    ),

    OpenFile = Json.Document(APICall),

    GetToken = OpenFile[access_token]
in
    GetToken

The result is the Token to use in the next Web Call, which I believe you show this is working.

If your requires a Json file in the body, try something like this to preview the Json file before including in the body.

let
    Source = Json.FromValue([username="<Useranme>", password="<Password>"]),
    #"Preview Json" = Text.FromBinary(Source)
in
    #"Preview Json"

[Daryl-Lynch-Bzy]

Hi @acc-int-ail - you have not quite followed the example in the link provided.  It is difficult to help properly without fully understanding the required API syntax.  I have found it help to test the REST API syntax in VS Code using HTTP to really understand the expected structure and then translate to Power BI.  Here are some thought on each image above:

Image 1 - you will normally find the that the Text from Binary is used with Json.FromValue - PowerQuery M | Microsoft Learn before provide into the Web.Content function.  But I don't think it will be right to use the Content Type application/json.  The Content Type in Image 3 is what I would expect an API of this nature.

Image 2 - I am not sure about this because you seem to supply a token to retrieve a token.

Image 3 - This appear to have the right content type to request a token but the body is all wrong.  You should be trying to supply the grant_type=password&username=###&password=### as the previous Suppot Ticket suggests.

Image 4 - Authorisation is a different approach to previous images.  The previous images would be POST API the content is submitted.  Authorisation is Header use for GET and POST.

[acc-int-ail]

Thank you for the response.

So I have only been sandboxing with the API in postman, and it works.

Maybe I should make it clear that the API requires Basic Auth in order for me to retrieve the Access token (that's why it looks like I use a token to get a token in img2, it's just the username and pw encoded), and in Postman I just add that information to the application (pw & username) so that is part of why I'm having a hard time translating how to do this..

So I use one URL to get the token, with basic auth, and then with that response I get the token. And with that token I can retrieve the data that I want. I'm not sure if I made anything clearer.