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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rpboyer2
Frequent Visitor

PowerBI REST API & Access Token with Power Query

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 TokenPBI 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 DocumentationPBI 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 FunctionPBI Workspace Data with GET AccessToken Function
Here is my 'GET AccessToken' function code.GET AccessToken FunctionGET AccessToken Function
The function seems to work perfectly fine as it returns the expected Access Token when invoking the function.
GET AccessToken Function InvokedGET AccessToken Function Invoked
I have the following Azure App 'API Permissions' configuredMicrosoft Azure App API permissionsMicrosoft 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 OverviewMicrosoft Azure App Certificates & secretsMicrosoft Azure App Certificates & secrets
My 'GET AccessToken' function also works when substituting the values in Postman for testing.PostmanPostman
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. 

20 REPLIES 20
GJA
Helper II
Helper II

Hi, 

 

does anyone have the solution to this post ? I have the same issue. I get a token but it doesn't work when I use it in power query. I got the message : DataSource.Error : Web.Contents ... (500) : Internal Server Error.

 

When I use a token generated with try it, it works.

 

Any solution please ?

messes
New Member

Your function is working, but you are missing an item in the body. Using your example I only got returned a empty token missing the roles, when tested with JWT.ms .   After adding the resource field in I started getting a working token and could reuse it (see second script block)

 

(Scope as text, Resource as text) => 
//I moved the other items to parameters which is why you don't see them here. 
let

    //POST Request
    url = "https://login.microsoftonline.com/"&tenant_id&"/oauth2/token",
    body = [
        scope = Scope,
        resource = Resource,
        client_id = client_id,
        client_secret=client_secret,
        grant_type = "client_credentials"
    ],

 

    Source = OData.Feed("https://graph.microsoft.com/beta/security/secureScores?$top=1", 
        [ Authorization = "Bearer " & Text.From(AccessToken("https://graph.microsoft.com/.default","https://graph.microsoft.com"))],
         [ ExcludedFromCacheKey = {"Authorization"}, ODataVersion = 4, Implementation = "2.0" ]),
FionaE
New Member

Hi

I followed the exact same process but I cannot even get the get access token function to work. My GET Access Token code is exactly the same as yours, but with my own credentials. It just comes up with this message when I click done.

 

FionaE_0-1684729842062.png

Any help would be greatly appreciated.

@rpboyer2 @ams1 

I was able to get past this issue with the help of Microsoft Support. My apologies i havent been able to share the solution in full detail with the community. I've been swamped in both my work and personal life. Contacting Microsoft Support is what i would recommend. Also i can offer to forward you my email chain of discourse with them which might help contextually how i was able to resolve the issue.

If you could forward this to me as well that would be great. tlepers@positionpartners.com.au

Hi,

 

I'm having the same issues and would appreciate if you could forward the email exchange you had with MS Support. Please send to dospencer@rollins.edu.

Anonymous
Not applicable

hey, I'm running into the same issue, but I'm trying to get Power Automate to generate the token, so that I can run some automations through that way. The access token that I'm generating is significantly shorter than the one generated by the Rest API Try It feature.

Can you email me that same thread from Microsoft Support?

rick.loveday@gmail.com

Thanks so much @rpboyer2 that would be amazing! accounts@bettagroup.co.nz

ams1
Super User
Super User

Hi @rpboyer2 

 

I think the problem is that you used ContentType instead of #"Content-Type"

 

Replace the corresponding part of your code with below:

 

...
                    Headers = [
                        Accept = "application/json",
                        #"Content-Type" = "application/x-www-form-urlencoded"
                    ],
...

 

 

Everything else seems ok - we'll see after you try the above.

 

Please mark this as ANSWER if it helped.

 

P.S.: next time please paste your PowerQuery code as text and not as screenshot 😊

rpboyer2
Frequent Visitor

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'
Postman BadRequest GroupDetails Required Parameter.PNG
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.
Postman MicrosoftOnline.png

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?

 

ams1_0-1678115160645.png

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
Frequent Visitor

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 ErrorGET AccessToken Function Manual.PNG

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
Frequent Visitor

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

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

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"

 

 

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?

 

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?

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





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.

API Permissions.JPG

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors