Generating Access Token for Power BI REST API in Power BI Desktop

[DaniloSantosBRS]

Good afternoon,

I don’t have much experience with Power BI, I’m trying to create a REST API that allows me to access the data from my report through Power BI Desktop.

I’ve already created the application in Azure, provided all necessary permissions, enabled access and ID tokens in authentication, and provided administrator consent, so far so good.

I have the following function to generate the token:

()=>
	let
		tenant_id="...",
		client_id = "...",
		client_secret = "...",
		scope = "https://analysis.windows.net/powerbi/api/.default",
		resource = "https://analysis.windows.net/powerbi/api",
		url = "https://login.microsoftonline.com/" & tenant_id & "/oauth2/token",

		body = [
			grant_type = "client_credentials",
			client_id = client_id,
			client_secret = client_secret,
			scope = scope,
			resource = resource
		],

		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

It generates a token of approximately 1342 characters.

The problem is when I use this token for a simple group request for example:

let
    access_token = GetAccessToken(),
    url = "https://api.powerbi.com/v1.0/myorg/groups?$top=100",

    GetJson = Json.Document(
        Web.Contents(
            url, [
                Headers = [
                    #"Authorization" = "Bearer " & access_token
                ]
            ]
        )
    ),

    table = Table.FromRecords({GetJson})
in
    table

The result is a table with @Data.count = 0, in other words, no results. If I try to access anything else the result is a message “Expression.Error: Access to the resource is forbidden.”.

However, when logging in on the documentation page (Groups - Get Groups - REST API (Power BI Power BI REST APIs) | Microsoft Learn) through “Try it”, a token of 2614 characters is generated and I can make any query and bring everything I need. Even if I copy this token into Power BI Desktop I also get the same result. I could use it in a fixed way, however it expires and it doesn’t make sense for me to have to go to the documentation page to copy the token every time it expires, or worse, have to tell my client to do this…

Apparently, via login with username and password on the documentation page, many other permissions and rules enter the token so it is larger.

The question is, how do I generate this same token from the documentation in Power BI Desktop? Is this currently possible? Am I doing this wrong?

Anyway, I count on the help of a kind soul, as I’ve been trying to solve this for days.

Thank you in advance!

[Bipin-Lala]

Hi @DaniloSantosBRS,

Yes, it is possible to access Power BI REST APIs from Power BI Desktop and automate the generation of tokens to fetch data into the reports.

Getting an auth token from the "Try It" section in Microsoft Learn Documentation is a great way to fetch data and experiment with things in Power BI, but the generation of an auth token needs to be automated to refresh reports continuously.

In our organization, we are using PowerShell scripts that use App Registrations to connect to Power BI read-only admin APIs and fetch the necessary data in the form of JSON files, which are then imported into Power BI.

If you want to automate the generation of auth token in Power BI, I suggest that you could go through a few of the following links and see if you can implement the solution they suggest - 

• https://pbi-guy.com/2023/03/25/connect-with-power-bi-to-the-power-bi-rest-api-with-a-service-princip...
• https://www.youtube.com/watch?v=2RZkc_qrV1g&t=603s&ab_channel=BIElite
• https://www.youtube.com/watch?v=N8qYRSqRz84&ab_channel=DhruvinShah
• https://community.fabric.microsoft.com/t5/Developer/To-get-Power-BI-API-Access-Token-dynamically-mak...

I believe the above links should help you in implementing your desired solution. Let me know how it goes for you!

[DaniloSantosBRS]

Thank you for the assistance @Bipin-Lala,

I followed the steps from the first link to generate the token, and it seems to have worked the same way; the function generates a token without errors.

The videos from the second and third links show how to authenticate using a username and password, but they're from 3 years ago. In the tests I conducted using this method, it didn't work; it returned a message related to authorization code. I believe this might be due to two-factor authentication, which prompts the user to access via a browser.

In the fourth link, it shows how to obtain the token via client_credentials, which is similar to the first method, and this worked.

Perhaps the issue lies not in the code but in some permission or option that I should enable either in Azure or in Power BI. It's strange because I'm the account administrator; I should have all the privileges.

Implicit Grant and Hybrid Flows



Workspace administrator



My REST API on Azure



Permissions in Azure