PowerBI REST API & Access Token with Power Query

[rpboyer2]

Greetings,
I'm looking for some input regarding obtaining data from the PowerBI REST API with a function to automate Access Token generation. I can get data from the API with Power Query using the following M code.
PBI Workspace Data with Try It Token
In this case I'm obtaining the Access Token manually through PowerBI's REST API Documentation 'Try It' feature.
PBI Workspace Try It Documentation
Given i can obtain the expected data with that Power Query M code i keep everything the same but this time leverage the 'GET AccessToken' function i created. I recieve the following error. Selecting 'Connect' does nothing. The error persists.PBI Workspace Data with GET AccessToken Function
Here is my 'GET AccessToken' function code.GET AccessToken Function
The function seems to work perfectly fine as it returns the expected Access Token when invoking the function.
GET AccessToken Function Invoked
I have the following Azure App 'API Permissions' configuredMicrosoft Azure App API permissions
I also am getting Client ID, Tenant ID, Client Secret Value from the Azure App informationMicrosoft Azure App OverviewMicrosoft Azure App Certificates & secrets
My 'GET AccessToken' function also works when substituting the values in Postman for testing.Postman
Everything seems fine independently but when trying to make the connection tying it together it doesnt work. There are enough examples on forums and youtube vidoes which all demonstrate the way i concatenate my invoked function result to the "Bearer " string is correctly done. I'm at a total loss as to what is going wrong. Thank you for your time, any input is greatly appreciated. 

[rpboyer2]

Thank you for the suggestion! Here's the update code. I didnt get a different result.

() =>
let
    //Found in Portal Azure site under App Registration: PBIAdminAPI-Dev
    tenant_id = "xxxxxxxxxxxxxx.....", //Overview Tab: Directory/Tenant ID
    client_id = "xxxxxxxxxxxxxx.....", //Overview Tab: Application/Client ID
    client_secret = "xxxxxxxxxxxxxx.....", //Certificates & secrets Tab: PowerBI secret Value

    //POST Request
    url = "https://login.microsoftonline.com/"&tenant_id&"/oauth2/v2.0/token",
    body = [
        grant_type = "client_credentials",
        scope = "https://analysis.windows.net/powerbi/api/.default",
        client_id = client_id,
        client_secret=client_secret
    ],

    //Get token
    GetJson =
        Json.Document(
            Web.Contents(
                url, [
                    Headers=[
                        Accept="application/json", 
                        #"Content-Type"="application/x-www-form-urlencoded"
                    ], 
                    Content= Text.ToBinary(Uri.BuildQueryString(body))
                ]
            )
        ),
    access_token = GetJson[access_token]
in
    access_token

My current gut feeling is that the problem lies outside powerbi/query; something to do with permissions of the token or parameters of the PowerBI API request. This is because i found that when testing on Postman i get some additional information contextually like:

'Bad Request - Group Details is a required parameter'

I'm trying to query this fuction: https://learn.microsoft.com/en-us/rest/api/power-bi/admin/groups-get-groups-as-admin. It specifies the URI Parameters as: top, expand, filter, skip. Nothing about Group Details so that error message confuses me. It also mentions that the 'Required Scope' is: Tenant.Read.All or Tenant.ReadWrite.All. I found that different Post URL return different info back. 'https://login.microsoftonline.com/{TENANT ID}/oauth2/v2.0/token' returns minimal  information compare to 'https://login.microsoftonline.com/{TENANT ID}/oauth2/token' which does include 'Scope.' So the second confirms im meeting the Scope criteria. However both keys return GroupDetails parameter error.

[ams1]

Hi @rpboyer2 

Good that we got that potential issue out of the way.

Given Postman is one of the most solid foundations for building PowerQueries connecting to APIs (and that you're using it), please be so kind to post here the Postman generated cURL statement for a successful request (don't forget to mask out confidential info!). Based on that we'll be able to propose the equivalent PowerQuery code.

So basically, you want a function that does what your below Postman successfully does, right?

If yes, please post the cURL generated by Postman just to be sure.

P.S.: above requests remain valid, but after re-reading again the conversation I think I've noticed you had some doubts ref the "PowerBI request". One way to see how the PowerBI request looks like is to setup a proxy of some sort, ex.:

• if you're running PowerBI locally:
  • you could use Fiddler
  • OR you could spawn a webserver in your preferred language (ex. python, even PowerShell etc.) and set the URL to your local webserver where you log things (this is how I usually debug)
• if you want to test a service connection, you need to get a bit more creative:
  • maybe a cloud lambda/function that PowerQuery connects to...

But usually if you get it working locally in Postman, you should be able to get it working on PowerBI (at least locally).

P.P.S. 😁: When you press Connect and still get the "Access web content" dialog, I think that's a sign you're getting 301 Permission denied

Finally: others are of course free/invited to jump in anytime to help out fix this 🤗

Please mark as answer the relevant post(s) that helped you find a solution

[rpboyer2]

Postman is returning the Access Key just like I'm able to get in PowerQuery. However it isnt returning any data from the PowerBI API when using the Access Key just like my PowerQuery issue. What is very confusing is that the only way i've been able to get data at all from the PowerBI API is when i use the 'Try It' feature from the documentation page & copy the Access Key generated. I can then Paste it into PowerQuery overriding my function result and then i get data. However if i take that same key that is returning data in PowerQuery and use in Postman i get the Bad Request - Group Details Parameter is required Error

[ams1]

Hi @rpboyer2 ,

Ok, now I think I see:

1. so not even Postman can return a valid token similar to the one returned by the "try it" feature, right?
2. using the "try it" token inside PowerQuery it works, but in Postman it doesn't?

[rpboyer2]

You understand the issue 100%. I've opened up a support ticket and will update here if i'm able to resolve this issue.

[BarisT]

And did you get an answer? I Have the same problem facing.

[dospencer]

The thread is long so I'm not sure what is the exact issue that you are facing. My issue was resolved and I am sharing what works for me.

I am able to get and access token via REST API calls and use that token in subsequent API calls.

For the Access token:

POST to "https://login.microsoftonline.com/<tenantid>/oauth2/v2.0/token"

Query Parameters:

"grant_type" = "client_credentials"

"client_id" = <Your client id>

"scope" = "https://analysis.windows.net/powerbi/api/.default"

"client_secret" = <Your client secret>

Headers

"Content-Type" = "application/x-www-form-urlencoded"

For the calls to subsequent REST API

Headers:

"Authorization" = "Bearer " + <AccessToken received the  POST>

"Content-Type" = "application/json"

"Accept" = "application/json"

[wgjunsay]

Hello, I tried this one in Postman but the access token I got a "401 Unauthorized" response. Any clue on what I might be doing wrong? 

[q2bi]

Did you solve this problem? I still have the same error "401 status code" after trying all the solution in this post. Looking forward to your reply. Thanks.

[rpboyer2]

Send me your email in a private message if you would like the email chain. The general advice i can give you is that that for my instance the documentation stated i needed certain permissions for a function to work... but it didnt work until i removed them which is obviously confusing. 

[BarisT]

Bro, you just saved me so much time. Thank you. I was passing my username and password in the body. This was not needed in the end. How much does it cost to gather this access token each time?

[dospencer]

I have no idea of the cost to gather the access token. I get the token, store it in a variable and use it in several subsequent REST API calls. I do not get a new token for each API call. I don't know the specific time to live or if this is configurable but one invocation is sufficient for me based on what I'm doing.

How would I find out the cost for getting the access token? Is there a place in the Azure Portal that would show that information?

[BarisT]

What I would do is to look out on your invoices in azure admin portal in the billing and invoices. The billing is specified regarding your usage. But i still doubt about the token costs. If i have some update about this after 1 month i could write back from here.

Which app permissions do i need to gather the data from microsoft power bi? E.g. do i need additional permission to write in the database next to the below permissions when i use for example this link Groups - Get Groups - REST API (Power BI Power BI REST APIs) | Microsoft Learn

Power BI Service (2)
Tenant.Read.All
Tenant.ReadWrite.All

[dospencer]

The attached image shows the permissions that were required for my use case. The Microsoft documentation normally indicates the permissions that are required for the specific APIs that you will use.