Connect API oauth2 to pull data

[Falcon]

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.

[SpartaBI · Accepted solution]

@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

[Falcon]

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

[SpartaBI]

My pleasure.

This Is SpartaBI !

www.spartabi.com

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

[Falcon]

Hello SpartaBi,

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

[SpartaBI]

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.

[SpartaBI]

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 & "
}",

to

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

?

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

[Falcon]

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"

}

[SpartaBI]

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

[Falcon]

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

[SpartaBI]

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

[Falcon]

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

[SpartaBI]

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

[Falcon]

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