Forum Discussion
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 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.
38 Replies
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" ]),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 ?
Hello there,
For those who want the solution follow the above rpboyer2 's instructions and just change the POST URL for this:
Old URL: https://login.microsoftonline.com/"<your-tenant>"/oauth2/token
New URL: https://login.microsoftonline.com/"<your-tenant>"/oauth2/v2.0/token
That work for me. In any case you can contact me on LinkedInHave a great day 🙂
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 😊
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.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
- if you're running PowerBI locally:
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.
Thanks so much rpboyer2 that would be amazing! [email protected]
you must create it in the azure portal
Thank you very much for quick respond, but my question was: I don't have any secret created, is that mandatory? Or where can I get that secret. to rephrase, I know how to save a secretes and certificate, but where can i get that secret so that i can save it. I currently don't have a secret saved in my application. Is the secret something like your API key or your some password (and can you say, where can i get those?)
hello, could. ou send me solution from MS.
´Many thanks
Private message me your email
Hi. I got a similar problem. Share with me please how you solved this issue to my email - [email protected].
Many thanks
