Automating Export To File With Power BI Pro

How to export reports programmatically with a Power BI Pro license only and send it to an arbitrary number of users via email using Azure Data Factory or Synapse Pipelines

Export, store and email Power BI report (Image by Author)

Introduction

Newest self-service Business Intelligence tools enable us to create very dynamic and interactive reports that are easily accessible. Yet, organizations often still prefer static reports for archiving purposes or to send an automated email including the report directly to the intended recipients after refreshing data.

In Power BI Service, programmatically exporting reports to a file is natively supported by using a dedicated REST API. This method works as long as the report resides on a Power BI Premium or Power BI Embedded capacity, but not on a shared capacity using Power BI Pro or Premium Per User licenses. The only supported alternative is using Subscriptions to send automated emails at set intervals with snapshots of the current report. However, the problem here is that this will never be precisely time aligned with the data refresh nor is it very flexible or customizable. It further requires all recipients to have a Power BI license.

On the other hand, developing the export and email distribution on your own, would allow you to make the distribution process actually part of the entire data pipeline which makes it perfectly time aligned and data would be always up-to-date when exporting and sending out reports. Further, recipients don’t necessarily need a Power BI license as well as any other additional content could be easily included. In this article, I will show you how you can achieve that with a Power BI Pro license only.

Before we jumping into it, I would just like to mention that this is not officially supported by Microsoft.

Prerequisites

1. Azure AD User with Power BI Pro license and multi-factor authentication (MFA) turned off.
2. Power BI Workspace including a Power BI Report on a “normal” shared capacity with Admins rights assigned to the User above.
3. Azure App Registration including client secret and “Allow public client flows” set to allow your application to silently authenticate as the user above and use the Power BI REST APIs. There are no additional API permission required since those are taken from the user itself — unfortunately it doesn’t seem to work with a service principal.

Solution

The idea to get this working is quite simple, we just reproduce the REST API calls happening behind the scenes when exporting a report via the graphical user-interface (GUI) within the Power BI service.

Trigger Export from UI (Image by Microsoft)

By starting the export via the button click a series of REST API calls gets triggered including some custom parameters such as the export format (PDF or PPTX). First, is triggered a POST request starting the export, followed by a number of GET request checking the export status until the export is finished which finally triggers a single GET request to retrieve the actual file.

Get Export Status (Image by Microsoft)

This is what the pipeline looks like in Azure Data Factory bringing the same result and storing the exported report directly in an Azure Storage.

Azure Data Factory Pipeline (Image by Author)

1. First, we need to request a token in order to communicate with the Power BI REST API. The authentication happens via the registered Azure App and the user credentials.
2. The Power BI REST API uses redirections behind the scenes depending on the region which we read out in this step.
3. This POST request triggers the actual export of the given report.
4. This loop gets the export status and goes on until the export is finished an ready to be downloaded.
5. This GET request downloads the actual file content and saves the file to a given directory in a linked Azure Storage.

The entire process is very similar to the way how it works on a Premium or Embedded capacity. The exported report file you can then send out as an email attachment by triggering an email workflow built with Logic Apps or natively via the Microsoft Graph API.

Template

You are very welcome to download this pipeline template and adjust it to your needs. You just need to select/create two linked services whereof one is a simple Azure Storage without any specific configuration and the other one is a HTTP Linked Service with the following configuration:

HTTP Linked Service (Image by Author)

The pipeline includes a set of parameters which also need to be populated to get it working. If you don’t want to include the password in plaintext, you can extend the pipeline to use Azure Key Vault instead.

Conclusion

This method allows you automatically store and send reports to as many people as you want, from or outside your organization and whether or not having a Power BI Pro license. It can also serve for archiving report snapshots at any given time interval to preserve historical states.