USE PYTHON TO GET DATA FROM POWER BI APIs

…such as the what a user or service principle has access to

IN THIS ARTICLE: CALL POWER BI REST API FROM PYTHON

  • Creating a Service Principal for use with the Power BI REST APIs / Admin APIs
  • API Permissions & Power BI Tenant Settings for the Service Principal to use the APIs
  • Authenticating with a Service Principal (or user) from Python using azure.identity
  • Writing a HTTP GET requestfrom Pythonto the Power BI APIs
  • Normalizing the response json to a table / dataframe.
Example result dataframe for workspaces obtained with the Admin API
Example result dataframe for workspaces obtained with the Admin API

CONTEXT

I recently watched a livestream by Haven’s Consulting where Matthias Thierbach explained an easy way to try the Power BI APIs from Visual Studio Code. I use the PowerShell cmdlets , but had not used the APIs as such. This filled in some missing pieces of the puzzle for me to get started trying this out in Python, something I’ve been meaning to do for over a year, now.

A few blog posts speak about this topic, already, but I had so many issues getting simple things to work that I thought to highlight the specific, nuanced points I struggled with regarding access & activity logs.

TUTORIAL

STEP 1: PREPARE A SERVICE PRINCIPAL (SP)

If you’ve never set up a service principal or worked with the APIs before, it’s understandable that it feels daunting. There are a lot of options, and the documentation gets really confusing - at times even contradictory. It isn’t so bad, though. All you have to do is register a new app (create the Service Principal; SP) with the correct API permissions, create a security group for it (or add it to an existing SP security group), and modify the Power BI Admin Portal tenant settings, appropriately, so the SP can use the APIs.

The below settings worked for me. Note the following:

  • The ‘Allow Public Flows’ option is not necessary depending on how you authenticate. When disabled, the Client Secret and Interactive Browser authentication methods still work, for me.
  • I had some issues with the API permissions. Granting admin consent resulted in 401: Unauthorized responses, which is rather strange. I did not need to add Delegated API permissions for specific artifacts; once the SP had Tenant.Read.All or Tenant.ReadWrite.All, I was able to get the data I needed. This may be different if you are doing POST requests to write changes to the service, though.
  • It’s a best practice to store secrets in your Azure Key Vault You can use the Azure Key Vault secret client library for python to get your secrets programmatically, so they aren’t stored explicitly as string variables in the code. This is a better security practice.
Register the app, set the API Permissions, and save the tenant ID, app client ID, client secret value
Register the app, set the API Permissions, and save the tenant ID, app client ID, client secret value
Add the SP to a security group for the Power BI admin settings
Add the SP to a security group for the Power BI admin settings
Adjust the Power BI admin settings, appropriately.
Adjust the Power BI admin settings, appropriately.

STEP 2: WRITE AN AUTHENTICATION FLOW

There’s a wonderful blog by datalineo that educated me about the existence of the azure.identity library for handling this. It’s simple, easy and it works. For authenticating, this library provides you with various options. They’re all listed on the docs site, but I’ll highlight the below:

Service Principals

Users

The below approach uses the ClientSecretCredential with the SP we just created. You can also try it for yourself using InteractiveBrowserCredential class, but this requires you take manual action to authenticate in the default browser.

STEP 3: WRITE THE HTTP GET REQUEST

For this you can just use the requests library to pass two arguments:

*Note for the Admin API - *the Admin API requires some additional syntax:

  1. TopN Argument: i.e. $top=3000 The end of the API endpoint must have a TopN argument. You have to loop through the results if you return more than 5000 results, or you exceed the request limit.

  2. Expand Argument: i.e. $expand=users,reports,dashboards,datasets

The first argument should have a question mark (?) before the dollar sign ($). Multiple arguments must be separated by an** ampersand (&).**

STEP 4: PARSE AND WRITE THE RESPONSE

The response is returned as an object which you can handle as a json using the json library. From here, you can flatten the json object to a dataframe (table) and/or output the result.

EXAMPLE

The below is a python script to get Power BI workspaces that the service principal is a member to. If you want to test it, add the SP as an Admin to 1 workspace, replace the string variables with your own code, and execute the below: