To get Power BI API Access Token dynamically, make sure to do this

[uahmed114pbi]

Im sure many people have struggled here to get this answer, so have I. I was finally able to resolve it with the help of microsoft support. I saw many posts here but none had the solution or had partial solutions, so I wanted to create a post that would lay it clear cut for anyone else looking for the same information. I watched this video to do this, which this person does well but there are a few things that weren't exactly clear, which I want to clarify below:

Power BI API tokens expire every hour, so it's quite annoying and not practical at all to have to copy paste the token all the time. This can be done dynamically in Power Query. For that, you'll need to create and register an app in PBI service and, which I'm not going to get into, watch the video I linked above to see how its done. The tricky part is that in the video, he uses grant_type = password. This would not work for me and was the cause of confusion. 

Some steps to consider: 

If your organization has MFA enabled, then you either need to disable MFA or you cannot use your username/password to connect, so the grant_type = password cannot be used in this case. 

If you use grant_type = password and MFA is disabled, then you need to also make sure the app has Tenant.Read.All API permission enabled, to be able to connect. 

If you cannot disable MFA (which was the case for me as our org wouldn't allow that ofcourse), then using the grant_type = client_credentials is the way to go. You'll need to provide the client_id, client_secret, scope and tenant_id. But make sure to REMOVE Tenant.Read.All from the API permissions.

When using service principal, you CANNOT have Tenant.Read.All permissions allowed in the app reg. This was the problem for me. I didn't know about the MFA, so I had Tenant.Read.All permissions allowed but it still didn't work. Then once I tried with grant_type = client_credentials, it still wouldn't work because I needed to remove the Tenant.Read.All permissions for it to work. 

To summarize: 

• When connecting with grant_type = password, you need to disable MFA and have Tenant.Read.All permissions enabled in the API Permissions. If you cannot disable MFA, then use client_credentials grant_type.
• When connecting with grant_type = client_credentials, Tenant.Read.All must be disabled and provide client_id, client_secret, tenant_id and scope. 

Hope that clears things up!
 

[MargaritaGG]

Hi, can you please advice what access should be assigned for this to work?

[Stanley_parmar1]

https://api.powerbi.com/v1.0/myorg/groups/{group_id}/reports/{report_id}/GenerateToken
I use the API to get the Token For power Bi Report To embed over the portal using the login API of the 

https://login.microsoftonline.com/"& TenantID & "/oauth2/v2.0/token/

From This API I get the access Token, But when I call Generate Token API to get the Token I get the 401 Error I don't get the solution for this what I need to do?

[v-rzhou-msft · Accepted solution]

Hi @uahmed114pbi 

Thank you for your sharing. I think we can try code as below to get access token in Power Query Editor. Then use this token as parameter when you get data by REST API. Access token will refresh new each time you refresh your report.

1. Get Access token in Power BI Desktop:

() =>

 

let

    body = "client_id=" & #"App ID"

    & "&scope=https://analysis.windows.net/powerbi/api/.default&client_secret=" & #"App Secret"

    & "&grant_type=client_credentials",

    Data= Json.Document(Web.Contents("https://login.microsoftonline.com/"& TenantID & "/oauth2/v2.0/token/",

    [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(body)])),

    access_token = Data[access_token]

in

    access_token

2. Video: How to get access token and call Rest API by dynamic access token in Power BI Desktop?

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MargaritaGG]

Hi,
In my case App registration was done not by me, but Tenant.ReadAll is enabled (so as I understand would need to request to disable that) -- what access should be left?
I tried using this in Power Query (I have MFA enabled account) and I get this (does not let me to proceed with anonymous)

Can you please advise?