Leveraging Power BI REST APIs: Python automation for dataset refresh and Microsoft Teams notification

November 9, 2023 Guilherme Joaquim

Context

I have been playing around with the Power BI REST APIs for the past weeks. It’s been really fun so far (I wish there were more APIs for the App section though). I was mostly interested in refreshing all the datasets (semantic models) in a dedicated workspace in Power BI Service in the end of my data pipeline in Synapse. Also, I wanted to check if the refresh from the datasets were completed and to be sent a notification in Microsoft Teams in case of a refresh failure.

There are of course many other ways to solve this problem and the solution in this post only requires a Power BI Pro license and MS Teams, so it doesn’t require the usage of Power Automate (HTTP is a premium connector).

In this post we are going to talk about the following topics:

Prerequisites and required set-up
How to use the Power BI REST APIs to refresh all your datasets using Python
How to receive a MS Teams message in case of a refresh failure

Prerequisites

Before we get into the technical fun part, we need the following set-ups:

Create a service principal to use the Power BI REST APIs
Allow the usage of the service principal in the Power BI (Fabric) tenant settings
Add the service principal or the Azure AD (or better say Microsoft Entra ID) in your workspace as admin

I won’t go through the steps above, because there are already plenty of great tutorials explaining how to do that. For example, this video from Guy in a Cube or refer to some other links in the end of this post.

How to use the Power BI REST APIs to refresh datasets using Python

After setting up the prerequisites above you are ready to start using the Power BI REST APIs. In this post we leverage Python to access the REST APIs, but it is also possible to use the Microsoft Power BI Cmdlets for Windows PowerSHell and PowerShell Core. Let’s deep dive into the technical part!

Required Python libraries:

import json, requests, time, pandas
from azure.identity import ClientSecretCredential

The “from azure.identity import ClientSecretCredential” is important for someone who has the Service Principal using a secret. The other option would be to use a certificate. In this blog post, we use a Service Principal with a secret.

Step 1: set up to access the Power BI REST APIs using Python

Here special thanks and credits to the blog post from Data Globlins. The blog post helped me with the following set-up.

Step 2: get the Power BI workspaces that the Service Principal has access to

The idea here to get all the Power BI workspaces that the Service Principal has access to, in order to see the workspaces and their ids.

Step 3: get and refresh the datasets inside the Power BI workspace

After getting all the Power BI workspaces, the code below is used to get and refresh all datasets from the selected workspace.

At this point we refreshed all the datasets inside the chosen workspace. However, technically the code did a POST request using the Power BI REST API to refresh the datasets, if you print the result from “dataset_update” you would get a status code equal to 200 (if the POST call was successful). But this does not mean that the dataset refresh was successful, for example in case of data source credential problems the dataset refresh would fail.

Ok, the golden question is how can I check the dataset refresh and receive a notification or message in case of a refresh failure? That is what we are going to cover next, stay tuned :)

How to receive a MS Teams message in case of a refresh failure

Step 4: get the last refresh history from the datasets

In this step, we are going to get the last refresh history from the datasets from step 3. The idea here is to save the dataset name and refresh Id in case of a refresh failure. Later this is going to be used to send a message in MS Teams.

You can find the Python code below:

Right now is time to send a message in MS Teams in case of any refresh failure.

Step 5: set-up MS Teams to send a message from Python

You need to have at least one Microsoft Teams team to follow the next steps. First, we need to add the app “Incoming Webhook”. This app enables us to share content in Microsoft Teams channels from external applications. In case this app is blocked for you to add, you might need to contact you IT department.

You can find below how to add the app:

Step 6: send a message in MS Teams in case a refresh failure

This is the final step, we are almost there :) let’s go back to the Python code and add the final step.

You can find the Python code below. It only sends a message in MS Teams in case there is any refresh failure.

I uploaded 2 datasets in Power BI Service that I knew the refresh would fail. Let’s see how the message looks like in MS Teams:

We did it :) you could also play around with the message format to add more information.

Conclusion

In this post we talked about the Power BI REST APIs and how to leverage Python to refresh datasets automatically and how to send a message in Microsoft Teams in case of a refresh failure. It is worth saying that you only need Power BI Pro license and Microsoft Teams to use this approach.

I am excited with the various possibilities to solve business cases using the Power BI REST APIs and the possibility to send messages in Microsoft Teams from external applications such as Python.

Thanks for reading!

DATA TRAVELING