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. 

[yagmur1]

Hi I have a quick question. 
I followed what you all texted, but I couldn't figure out what the client_secret is. Yes you put client secret in registred application, but what did you saved as a value, where can i find that?

[rpboyer2]

you must create it in the azure portal

[yagmur1]

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?) 

[rpboyer2]

I cant answer if you absolutely need the secret with certainty. It's been so long since i worked on this that a lot of the details are fuzzy in my memory. I know i needed the secret to get it work for the data i was pulling in my instance. If you want to get a secret please re-examine the picture uploaded. I circled the new client secret option for you.

[EduardoHuiza]

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 LinkedIn

Have a great day 🙂

[GJA]

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]

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]

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.

Any help would be greatly appreciated.

@rpboyer2 @ams1 

[rpboyer2]

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.

[bender1234]

Hi, I am facing the same issue. Could you please forward me the emails with MS support to bender42@gmx.de

Thanks a lot!

[FionaE]

Hi, Please can you send me the email thread with MS Support too - accounts@bettagroup.co.nz

[AISB]

Hi, could you pelase send it to me as well? ismat.zainal@gmail.com

[Badaga]

Could you send it to me too please? r2.rebeca@gmail.com

[wgjunsay]

Hello, I have recently been diving into this and I experienced the same issue. Can you please also forward me the the email thread you have with Microsoft Support? Thanks! I'll send you my email address via private message. 

[ICTJZT]

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

[dospencer]

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]

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

[FionaE]

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

[ams1]

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]

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.