topic Re: Connect API oauth2 to pull data in Developer

Connect API oauth2 to pull data

Falcon — Tue, 03 May 2022 15:39:05 GMT

Hello Everyone,

I am trying to pull API data using oauth2. I have been following different methods online without success. All I have are

token URL: https://xxxxxxxx/api/v2/oauth/token
client_id: xxxxxxxx
client_secret: xxxxxxxx

I am able to get the token and pull data in the Postman. However, I am not able to do that in Power BI. Can anyone provide me with a solution? Thanks in advance.

Re: Connect API oauth2 to pull data

SpartaBI — Tue, 03 May 2022 16:58:43 GMT

Hi @Falcon,

You'll need to use the M function Web.Contents in Power Query in order to call a rest API.
There are many examples of how to perform an OAuth2 flow in Power BI throughout the web.
For example (this one is using a user delegation- but you'll get the idea): https://www.linkedin.com/pulse/accessing-api-oauth2-using-m-language-power-bi-diogenes-santos

Re: Connect API oauth2 to pull data

Falcon — Thu, 05 May 2022 16:38:20 GMT

I followed the instructions, but I got "400 Bad Request" from the token URL from the script. Do you know why? I am sure that URL is correct. Below is what I got

DataSource.Error: Web.Contents failed to get contents from 'https://xxxxxxxxxxxx/oauth/token' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://xxxxxxxxxxxx/oauth/token
Url=https://xxxxxxxxxxxx/oauth/token

Re: Connect API oauth2 to pull data

SpartaBI — Thu, 05 May 2022 16:59:00 GMT

Hi @Falcon,

I'll need to see your actual code to figure it out.
Instead, have a look at the following code.
It will generate an OAuth2 access token from AAD, to the Power BI Service, using a service principal.
You probably need an access token to another scope or not to AAD at all...

let TenantId = "XXXXXX", ClientId = "XXXXXX", ClientSecret = "XXXXXX", Uri = "https://login.microsoftonline.com/" & TenantId & "/oauth2/v2.0/token/", Body = "scope=https://analysis.windows.net/powerbi/api/.default" & "&grant_type=client_credentials" & "&client_id=" & ClientId & "&client_secret=" & ClientSecret, OAuth2Response = Json.Document(Web.Contents(Uri, [Content=Text.ToBinary(Body)])), access_token = OAuth2Response[access_token] in access_token

Re: Connect API oauth2 to pull data

Falcon — Thu, 05 May 2022 17:21:18 GMT

Hello Sparta Bi,

Here is my code by following the above instruction. The "tokenUserName" and "tokenPassword" hold "client_id" and "client_secret" respectively. The "tokenAddress" holds the URL which is "https://xxxxxxxxxxx/mohcacc/api/v2/oauth/token". I don't have TenantId and scope as in your example. How do I get them?

let body = "{
""username"" = " & tokenUserName & ",
""password"" = " & tokenPassword & "
}",

getToken = Web.Contents(
tokenAddress,
[Content = Text.ToBinary(body)]
)
in getToken

Re: Connect API oauth2 to pull data

SpartaBI — Thu, 05 May 2022 17:33:39 GMT

Hi @Falcon,

The TenantId & scope are relevant for the AAD OAuth2 flow. In your case, you probably do not need them.

Did you try changing

let body = "{ ""username"" = " & tokenUserName & ", ""password"" = " & tokenPassword & " }",

let body = "{ ""client_id"" = " & tokenUserName & ", ""client_secret"" = " & tokenPassword & " }",

?

If this doesn't help, please share the successful "get token" postman request.

Re: Connect API oauth2 to pull data

Falcon — Thu, 05 May 2022 17:44:24 GMT

Yes, I did change username and password to client_id and client_secret as you suggested. I just got the error message

DataSource.Error: Web.Contents failed to get contents from 'https://xxxxxxxxxxx/mohcacc/api/v2/oauth/token' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://xxxxxxxxxxx/mohcacc/api/v2/oauth/token
Url=https://xxxxxxxxxxx/mohcacc/api/v2/oauth/token

In Postman, I was able to retrieve the token with the following script in body

{
"grant_type": "client_credentials",
"client_id": "xxxxxxxxxxxxxxxx",
"client_secret": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

}

Re: Connect API oauth2 to pull data

SpartaBI — Thu, 05 May 2022 17:54:44 GMT

Hi @Falcon,

Try this code

let ClientId = "XXXXXX", ClientSecret = "XXXXXX", Uri = "https://xxxxxxxxxxx/mohcacc/api/v2/oauth/token", Body ="{ ""grant_type"" = ""client_credentials"", ""client_id"" = """ & ClientId & """, ""client_secret"" = """ & ClientSecret & """, }", OAuth2Response = Json.Document(Web.Contents(Uri, [Content=Text.ToBinary(Body)])) in OAuth2Response

Re: Connect API oauth2 to pull data

Falcon — Thu, 05 May 2022 18:23:27 GMT

Here is the code I entered

let
ClientId = "xxxxxxx",
ClientSecret = "xxxxxxxxxxxx",
Uri = "https://secure9.aladtec.com/mohcacc/api/v2/oauth/token",
Body ="{
""grant_type"" = ""client_credentials"",
""client_id"" = """ & ClientId & """,
""client_secret"" = """ & ClientSecret & """,
}",
OAuth2Response = Json.Document(Web.Contents(Uri, [Content=Text.ToBinary(Body)]))
in
OAuth2Response

Here is what response I got

DataSource.Error: Web.Contents failed to get contents from 'https://secure9.aladtec.com/mohcacc/api/v2/oauth/token' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://secure9.aladtec.com/mohcacc/api/v2/oauth/token
Url=https://secure9.aladtec.com/mohcacc/api/v2/oauth/token

Re: Connect API oauth2 to pull data

SpartaBI — Thu, 05 May 2022 19:04:01 GMT

@Falcon

I've just noticed that the json was malformed + I've added a relative path for the auth.
Try this:

let ClientId = "XXXXXXX", ClientSecret = "XXXXXXX", Uri = "https://secure9.aladtec.com/", Body ="{ ""grant_type"": ""client_credentials"", ""client_id"": """ & ClientId & """, ""client_secret"": """ & ClientSecret & """ }", OAuth2Response = Web.Contents(Uri, [RelativePath = "mohcacc/api/v2/oauth/token", Content=Text.ToBinary(Body)]) in OAuth2Response

Re: Connect API oauth2 to pull data

Falcon — Thu, 05 May 2022 19:20:04 GMT

OMG that worked, thanks SpartaBI, you're such a big help

Re: Connect API oauth2 to pull data

SpartaBI — Thu, 05 May 2022 19:23:09 GMT

My pleasure.

This Is SpartaBI !

www.spartabi.com

https://www.linkedin.com/company/spartabi

Re: Connect API oauth2 to pull data

Falcon — Thu, 05 May 2022 20:15:35 GMT

Hello SpartaBi,

Can you help me a bit further? Now I got the token, how do I use it to retrieve the data?

Re: Connect API oauth2 to pull data

SpartaBI — Fri, 06 May 2022 09:09:11 GMT

Hi @Falcon,

With an OAuth2 token, you should probably do something like:

let Token = "XXXXXXXXX", DataUri = "https://secure9.aladtec.com/XXXXXXXXX", AuthHeaders = [Authorization = "Bearer " & Token], Response = Json.Document(Web.Contents(DataUri, [Headers = AuthHeaders])) in Response

You can create a different PQ query for the second API call, or add it to the same query.

If this doesn't work, please create a new issue with details regarding the API that should retrieve the data (e.g., a successful postman request with a hardcoded token). This makes it easier for community members to locate solutions to similar problems.

Tag me (using @) in the new issue, so I'll get a notification, and I'll try to look into it.