API Connection Won't Authenticate in Power BI Service

[sjschminke3]

I have a report with a single query connected to a web API.  It uses 2 functions, where it grabs a dynamic bearer token first and then passes that token to the API url.  Inside of Power BI this generates 2 web connections, which refresh perfectly fine locally in Desktop.

Here is the query:

=======================================================

let

GetBearerToken = () =>
let
authUrl = "https://login.microsoftonline.com/xxxxxxxxxxxxx/oauth2/v2.0/token",

clientid = "yyyyyyyyyyyyyyyyy",
secret = "zzzzzzzzzzzzzzz",
scope = "wwwwwwwwwwwwww",
granttype = "client_credentials",

requestBody = "grant_type=client_credentials" &
"&client_id=" & Uri.EscapeDataString(clientid) &
"&client_secret=" & Uri.EscapeDataString(secret) &
"&scope=" & Uri.EscapeDataString(scope)
,

authResponse = Json.Document(Web.Contents(authUrl, [Headers = [#"Content-Type"="application/x-www-form-urlencoded"], Content = Text.ToBinary(requestBody)])),

accessToken = authResponse[access_token]

in

accessToken,

GetAPIData = (token as text) =>

let

apiUrl = "https://dlr-xxxxxxxxxxxxxxx.azurewebsites.net/api/Project/xxxxxxxx/Summary",

apiResponse = Json.Document(Web.Contents(apiUrl, [Headers=[Authorization="Bearer " & token]]))

in

apiResponse,

RefreshTokenIfNeeded = (currentToken as text) =>

let

refreshedToken = currentToken

in

refreshedToken

in

GetAPIData(GetBearerToken())

============================================================

When I publish to Power BI service, it tries to authenticate them independently, which does not work because you need to fetch the token to authenticate the API url.  I've tried everything I can think of.  Any ideas?

[GilbertQ · Accepted solution]

Hi @sjschminke3 

You can use the example in the link below from Prathy where she explains how to store the token within a single power query table.

Calling Power BI API using Power BI Desktop to document Power BI Service – Prathy's Blog…

[Anonymous · Accepted solution]

Hi @sjschminke3 ,

Thanks for @GilbertQ reply!

@sjschminke3 Based on community experience, there are several approaches for you reference.

• Clear browser cache.
• Select Skip Test Connection when setting up.

    

• Set up a On-premises data gateway.Install an on-premises data gateway | Microsoft Learn

Here are some links for your reference:

Solved: Credential Error for Excel File hosted on One Driv... - Microsoft Fabric Community

API Unable to refresh in Power BI Service: The cre... - Microsoft Fabric Community

Failed to update data source credentials - Microsoft Fabric Community

Best regards,

Mengmeng Li

[sjschminke3 · Accepted solution]

In addition to the 2 above responses, I also had to change the data source privacy level to "Organizational" and it finally worked!  Thank you both!

[Anonymous · Accepted solution]

Hi @sjschminke3 ,

Thanks for @GilbertQ reply!

@sjschminke3 Based on community experience, there are several approaches for you reference.

• Clear browser cache.
• Select Skip Test Connection when setting up.

    

• Set up a On-premises data gateway.Install an on-premises data gateway | Microsoft Learn

Here are some links for your reference:

Solved: Credential Error for Excel File hosted on One Driv... - Microsoft Fabric Community

API Unable to refresh in Power BI Service: The cre... - Microsoft Fabric Community

Failed to update data source credentials - Microsoft Fabric Community

Best regards,

Mengmeng Li

[GilbertQ · Accepted solution]

Hi @sjschminke3 

You can use the example in the link below from Prathy where she explains how to store the token within a single power query table.

Calling Power BI API using Power BI Desktop to document Power BI Service – Prathy's Blog…