[Python] Using the Power BI API Without “Grant Permission” From Admin

[ibarrau]

La Data Web will show a bit of Python code in order to connect our work with the Power BI Service API. This post will help us to obtain the access token with pure urllib Python 3.7 code. What is special in this post is that we will use the Power BI API without the “grant permission” of the Azure/Office365 authority administrator.
In order to fulfill this goal and write some Python code, first we need to set up some configurations that will help us get through the Power BI API by enabling our own permission.

First Step: Register App in Azure
Log in at www.portal.azure.com with your Microsoft professional account. On the left pane of the portal the Azure Active Directory, you find the option -> App registrations -> New Registration, as shown in the image.

Write down the name for your app. For example, "PowerBI-API-Access", and then click Register. From that moment on, we will have an app in Azure that will help us to communicate with Microsoft, services or APIs depending on our permissions. Let's go to our app and click on "Add a permission".

Then, you can type "power bi service" in the textbox. Click on the result of the search.

It will take us to the following image, where we can add the permission that we will use to operate the API.

IMPORTANT: The only permissions that we can accept are the ones in the "Delegated Permissions" section. Let's pick up ReadWrite.All for Workspace and Dataset, so we can manage refreshes that are most commonly used.

Now, we will need to get an access token to interact with the API. To do that, we change the following configurations:

We need a publicly authenticated access post in the API.

Continue the configuration like this:

Don't forget to save the settings before you close them.

Second Step: Accept Permission
From here, we are going to copy and paste this modified link that will show us a screen to accept the requested permission in the app creation for our user and we will accept it. Please be aware that this link will work once per permissions, and you will have to change the parameters adding your client id (application id). This is the link:

https://login.windows.net/common/oauth2/authorize?response_type=code&client_id=[CLIENTID]&redirect_u...

The screen should look like this:

Now we are ready! After we accept these permissions, we are going to be able to obtain the access token and call the Power BI Service API.

Third Step: Get the token with Python

With the whole environment ready we just need to start the coding. The Microsoft recommendation would be using PowerShell, but we are going to use a sweet language that’s being used these days.

The example consists in building a script that will let us refresh a dataset in Power BI. This is really useful for big architectures that involve an orchestrator refreshing different services as a chain. In case you want to call another part of the API, you can check the documentation that will be easy to understand, once we have the access token.

Before checking the code in my GitHub, we must define five variables.

• power_bi_group_id = 'XXXX-XXXX-XXXX-XXXX'
• power_bi_dataset_id = 'XXXX-XXXX-XXXX-XXXX'
• power_bi_username = 'XXXX-XXXX-XXXX-XXXX'
• power_bi_password = 'XXXX-XXXX-XXXX-XXXX'
• power_bi_client_id = 'XXXX-XXXX-XXXX-XXXX'

The username and password are your Microsoft professional or educational credentials account, and the client_id is the one from the app we registered in Azure. Those are easy to fill, so we need just the group (power BI workspace) and dataset. To get this, we can call the get groups and datasets methods of the API, or we can log into the Power BI Service and copy the IDs from the URL, as I’m explaining next.

Logged in app.powerbi.com let's go into a dataset:

Once we are on the blank page, the URL is ready to be copied. Let's check it:

https://app.powerbi.com/groups/107c4556-aa47-43e8-b88c-a1fb7bc5f352/datasets/08110dd8-80fe-40f7-8b5c...

• Group_id = 107c4556-aa47-43e8-b88c-a1fb7bc5f352
• Dataset_id = 08110dd8-80fe-40f7-8b5c-2d97200b8d43

Now we have all the variables complete to obtain the token and run a refresh for the dataset. Check the Python code on my GitHub. It contains two methods: get_auth_token and refresh_dataset.

https://github.com/ibarrau/PowerBi-code/blob/master/Python/Refresh%20PBI%20python%203%20onpremise.py

Let's remember that one call to refresh a dataset in Power BI counts as a schedule refresh for the service. That's why you are only going to be able to call the method up to eight times, due to a limitation in free and pro licenses.

I hope this helps you to organize and orchestrate your process in one thread!

This post was created with the assistance of Martin Zurita (Data Engineer)