Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
acc-int-ail
Frequent Visitor

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

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

error message.png

 

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 datafirst connecting to the API, it works, I have a table of some dataimg1img1img2img2img3img3img4img4

 

 

 

 

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.

1 ACCEPTED SOLUTION
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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"

 



View solution in original post

3 REPLIES 3
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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
Resident Rockstar
Resident Rockstar

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.

 

 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors