cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User IV
Super User IV

Archive Power BI Reports with Power Automate

I recently had the requirement to save the monthly Dataset of a Power BI Report. I remembered a blog post by Chris Webb on how to use Power Automate to access the Power BI Rest API. The following post describes how I use this to create a monthly PBIX backup.

 

Register an app for the Power BI APIs

In the first step, we have to register a new application that can access the Power BI APIs of our tenants. I will not describe the process in detail. Take a look at the Microsoft documentation.

The application can be registered on page
https://dev.powerbi.com/apps

PowerBIAPIs_PowerAutomate_01M_20200705

 

  • Application Name: Enter the name of your application here
  • Application Type: Server-Side web application (for web apps or web APIs)
  • Home Page URL: Here you can enter the URL of your website / tenant
  • Redirect URL: First enter https://login.live.com/oauth20_desktop.srf, this will later be replaced by a Redirect URL from Power Automate
  • API access: I selected “Select all” to be able to use all API functions in Power Automate. You can also change access later using the Azure portal.

Now we “Register” the application and save the “Application ID” and “Application secret” somewhere safe. They will be needed later.

PowerBIAPIs_PowerAutomate_02AM_20200705

 

 

Creation of a Power Automate Custom Connector

Navigate to Power Automate and select “Custom connectors” under “Data” in the left navigation. Here we select the “Import an OpenAPI from URL” option from the “Create custom connector” menu.

PowerBIAPIs_PowerAutomate_03M_20200705

 

Enter a name for your connector and import the OpenAPI definition. An OpenAPI definition for the Power BI REST API can be found here. https://raw.githubusercontent.com/microsoft/PowerBI-CSharp/master/sdk/swaggers/swagger.json

PowerBIAPIs_PowerAutomate_05M_20200705

 

The next dialog requires some general information about the connector.

PowerBIAPIs_PowerAutomate_06M_20200705

 

I uploaded an icon for the connector and set the background color to: #F2C811

The next dialog is to configure security.

PowerBIAPIs_PowerAutomate_07M_20200705

 

Konstantinos has determined the following values for Azure authentication here.

  • Authentication type: OAuth 2.0
  • Identity Provider: Azure Active Directory
  • Client id: Enter the “Application ID” from the application registration
  • Client secret: Enter the “Application secret” from the application registration
  • Login URL: https://login.windows.net
  • Tenant ID: common
  • Resource URL: https://analysis.windows.net/powerbi/api
  • Scope: openid

After clicking on “Create connector” the Redirect URL is displayed. Depending on your location, this URL may be different.

PowerBIAPIs_PowerAutomate_08M_20200705

 

Add the Redirect URL to the previously registered Application. Sign in to the Azure portal https://portal.azure.com
“Azure Active Directory” -> “App registrations” -> “Name of our app”. Under “Authentication”, replace the “Redirect URIs” with the URL from Power Automate.

PowerBIAPIs_PowerAutomate_09M_20200705

 

Creation of the Power Automate Flow

Now we create the Power Automate Flow. First we determine the GUID of the workspace and the Power BI report that is to be archived. After opening the report, the values can be read from the address line.

PowerBIAPIs_PowerAutomate_20_20200705

 

For the monthly execution we choose a “Scheduled flow” in Power Automate.

PowerBIAPIs_PowerAutomate_10_20200705

 

Give the flow a name and set up the schedule.

PowerBIAPIs_PowerAutomate_11_20200705

 

In the flow, the Custom Connector can now be used with all Power BI API calls.

PowerBIAPIs_PowerAutomate_12_20200705

 

When you access for the first time, you must sign in and agree to the requested permissions.

PowerBIAPIs_PowerAutomate_13_20200705

 

You can then set up the following Power Automate Flow.

PowerBIAPIs_PowerAutomate_14_20200705

 

New Step: Export PBIX

Select the Custom Connector action “Exports the specified report from the specified workspace to a .pbix file.” and rename it “Export PBIX”.

  • groupId: GUID of the source workspace
  • reportId: GUID of the source report

New Step: Compose

Select the “Compose” action to convert the export body into the “multipart/form-data” structure required for the import.

  • Inputs:

 

{
  "$content-type": "multipart/form-data",
  "$multipart": [
    {
      "body": @{body('Export_PBIX')}
    }
  ]
}

 

 

New Step: Import PBIX

Select the Custom Connector action “Creates new content on the specified workspace from .pbix, .json, Excel, Rdl, or file path in OneDrive for Business.” and rename it “Import PBIX”.

  • groupId: GUID of the target workspace
  • datasetDisplayName: Name of the PBIX file to be created, I use the following expression for this.

 

concat('DIAD ', 
    formatDateTime(utcNow(),'yyyyMMdd') ,
    '.pbix')

 

 
  • importInfo: The output of the previous Compose step is inserted here
  • nameConflict: Abort

After creation, the Power Automate Flow can be tested.

PowerBIAPIs_PowerAutomate_15_20200705

 

A look at the Power BI workspace shows that the copy of the report has been saved successfully.

PowerBIAPIs_PowerAutomate_16_20200705

 

And one last thing: Konstantinos & Chris mention in their posts, if you get a 404 error (or 403 error), just wait a few minutes.

Links:

 

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors