PASS Pro Now Available

Welcome to the next evolution of PASS. Unlock exclusive training, discounts, and networking opportunities designed to accelerate your data career. Learn More >

The official PASS Blog is where you’ll find the latest blog posts from PASS community members and the PASS Board. Contributors share their thoughts and discuss a wide variety of topics spanning PASS and the data community.

Leverage Power BI Automation Using Power Automate and API

Introduction

For a Power BI tenant’s administrator, it's a challenging task to admin and maintain the environment. Many changes are taking place every day. New users are added, workspaces are created/deleted, datasets are refreshed, etc. How can we keep everything under control? What’s needed is automation. Through the joint use of APIs and Power Automate, it’s possible to leverage some common tasks automatically.

Power BI APIs

REST APIs provide service endpoints for embedding, administration and user resources. They are divided into groups based on the task category. For a full list of groups and operations, click here.

The image below outlines the structure of an API call for Power BI.

Which corresponds, for example, to the following URL:

https://api.powerbi.com/v1.0/myorg/apps

When queried, an API returns a JSON object. If you run this request:

GET https://api.powerbi.com/v1.0/myorg/reports/5b218778-e7a5-4d73-8187-f10824047715

You get the following response:

{
  "datasetId": "cfafbeb1-8037-4d0c-896e-a46fb27ff229",
  "id": "5b218778-e7a5-4d73-8187-f10824047715",
  "name": "SalesMarketing",
  "webUrl": "https://app.powerbi.com//reports/5b218778-e7a5-4d73-8187-f10824047715",
  "embedUrl": "https://app.powerbi.com/reportEmbed?reportId=5b218778-e7a5-4d73-8187-f10824047715"
}

In the example above, the API returns the specified report from “My workspace”. The API passes the report id and gets a JSON object with dataset id, report id, report name, report webUrl, and report embedUrl.

API available methods are: 
GET = Read
POST = Create
DELETE = Delete
PUT = Update/Replace
PATCH = Update/Modify

API and Power Automate

Power BI APIs can be used in different ways:

  • In a Power BI embedded application.
  • In a PowerShell cmdlet.
  • From a custom connector in Power Automate.

Hereinafter, I will show you how to create a custom connector in Power Automate with embedded API calls for automation.

Keep in mind that you need to know the URL and the structure of the API you want to add to the custom connector beforehand. Check the documentation to find out the actions you want to perform with the connector.  

Power Automate Custom Connector

Prerequisites for executing the tasks in this section are:

  • A Power Automate account.
  • A Power BI account. Pro it is not mandatory, but it is recommended for running API through workspaces.
  • Access to the Azure portal for your Power BI tenant.

The following steps have to be completed for creating a full working solution:

  1. Create a new connection in Power Automate.
  2. Register a web app in the Power BI developer portal.
  3. Create a custom connector in Power BI.
  4. Add API actions to the connector.
  5. Create a new flow with triggers, conditions, actions, …

Step 1: Create a Connection in Power Automate

Go to the Power Automate home page. Data > Connections > New connection.

Search for Power BI connection.

Click on the filtered row > Create.

Enter the required credentials in the pop-up window. Check on Data > Connections. Now, you have a new connection to Power BI from Power Automate.

Step 2: Register a Web App

Go to https://dev.powerbi.com/apps.

This opens the page for registering the app. Click Sign in for logging in, then click Next

Give your application a name. I called mine “Power BI Automation API”.

In the other fields, type the following:

Application type = Server side web application

Home page URL = https://www.powerbi.com

Redirect URL = https://www.powerbi.com

In the “API Access” section, you have to select the level of access for the application, Read only, Read and write, Create.

Once the selection is made, click Register.

Your application is registered, and it shows you the codes for Application ID and Application Secret.

Copy and paste these strings; you’ll need them later on.

Step 3: Create a Custom Connector in Power Automate

From the Power Automate home page, select Data > Custom connectors.

New custom connector > Create from blank > Give it a name > Continue.

Next, you have 1 out of 4 pages for the final definition of your connector.

In Page 1, fill in all the requested information:

Add an image for your connector (optional).

Define a background color (optional).

Scheme = HTTPS

Host = api.powerbi.com

Base URL = /

 

Move to Page 2 > Security.

Authentication type = OAuth2

Identity Provider = Azure Active Directory

Client id = the app application id you saved before

Client secret = the app application secret you saved before

Login URL = https://login.windows.net

Tenant ID = common

Resource URL = https://analysis.windows.net/powerbi/api

Scope = blank

Redirect URL = https://global.consent.azure-apim.net/redirect

Click Create Connector.

Move to page 3 > Definition

Add a New Action

Let’s say you want to use the following API: Groups – Get Groups. This API returns the list of workspaces you have access to. The API URL is: https://docs.microsoft.com/en-us/rest/api/power-bi/groups/getgroups.

Note: In API syntax, groups correspond to workspaces. Group is the old name for workspace in the early stages of Power BI.

Fill in the fields in the General pane:

Summary = A name for the action

Description = Description for the action

OperationID = unique name for the action

Visibility = none

Move downwards. Under Request select “import from sample”.

  

This opens a new window. Fill in the following data:

Verb = GET,

URL = https://api.powerbi.com/v1.0/myorg/groups

Click Import.

The API configures the following request:

  

Update the Connector. 

Move to Page 4 for testing the API.   

Select the connection you’ve created on Step 1.

On the lower left side in the page under Operation, you can find the list of actions associated the connector. So far, only one action has been created. Select the action name; for me it is GetGroups.

On the center of the page, click on Test operation. 

If it works correctly, the output is a JSON script with a list of your workspaces with the following attributes: id, isReadOnly, isOnDedicatedCapacity, and name. 

Step 4: Add API Actions to the Connector

Now that we have a working connector, let’s add another action to it. We want to invoke the following API: Reports – Get Reports in Group. This returns a list of reports from the specified workspace. Note that this API requires the workspace ID as a parameter to be passed to the call.

 API structure: GET https://api.powerbi.com/v1.0/myorg/groups/{groupId}/reports

The parameter groupid is the workspace id.

The response is a JSON object of the following format:

{

  "value": [

    {

      "datasetId": "cfafbeb1-8037-4d0c-896e-a46fb27ff229",

      "id": "5b218778-e7a5-4d73-8187-f10824047715",

      "name": "SalesMarketing",

      "webUrl": "https://app.powerbi.com/groups/f089354e-8366-4e18-aea3-4cb4a3a50b48/reports/5b218778-e7a5-4d73-8187-f10824047715",

      "embedUrl": "https://app.powerbi.com/reportEmbed?reportId=5b218778-e7a5-4d73-8187-f10824047715&groupId=f089354e-8366-4e18-aea3-4cb4a3a50b48"

    }

  ]

}

In Power Automate, open your custom connector Data > Custom connectors > Edit your connector.

Go straight to Page 3 Definition. All the other settings are still valid.

Under Actions, add a new action. Fill in the name, description and Operation ID.

Under Request, select Import from Sample.

Verb = Get

URL =

https://api.powerbi.com/v1.0/myorg/groups/{groupId}/reports

Click Import.

The request is then configured. Please pay attention to the groupid parameter. You have to pass it alongside the API call.

Update the connector.

Move to Test for testing the new action. Remember to copy a workspace id from your Power BI account.

In Operations, select your action, select the connection and paste the workspace id in the groupid textbox. Click Test operation.

If the status is ok, you will get the following output:

A JSON object with a list of all the reports inside the selected workspace.

Step 5: Create a New Flow Based on the Custom Actions

So far, you’ve done a good job. You’ve created a custom connector, adding some actions from REST API. Now it’s time to embed actions into a flow to achieve real automation.

In a business scenario case, the flow could be triggered by an event. For example, you could use the API to add/delete users from a workspace based on a Sharepoint list that somebody is maintaining. In such a scenario, the flow is triggered by the edits on the list.

From the Power Automate home page, create a new Flow. Create > Instant flow (Triggered manually as needed).

Give your flow a name, select Manually trigger a flow and click Create.

In the next window, add a new step.

Select Custom, and you see the list of your custom connectors.

Select your connector, and you have the list of available actions.

Select one of the action you’ve created before. I chose “Get groups”. The action is referencing the following API.

Add a new step > Parse JSON.

Content = body from previous step

Schema = JSON output from the API. In my case:

{

  "value": [

    {

      "id": "f089354e-8366-4e18-aea3-4cb4a3a50b48",

      "isReadOnly": false,

      "isOnDedicatedCapacity": false,

      "name": "sample group"

    },

    {

      "id": "3d9b93c6-7b6d-4801-a491-1738910904fd",

      "isReadOnly": false,

      "isOnDedicatedCapacity": false,

      "name": "marketing group"

    },

    {

      "id": "a2f89923-421a-464e-bf4c-25eab39bb09f",

      "isReadOnly": false,

      "isOnDedicatedCapacity": false,

      "name": "contoso",

      "dataflowStorageId": "d692ae06-708c-485e-9987-06ff0fbdbb1f"

    }

  ]

}

The last step could be, for instance, saving the outputted list in a repository either online or on-premises. It depends on your layout. Take into account that, in any case, you need to create a connection to your repository to make the next step work.

I saved the output in a txt file on my F: drive.

New step > Choose an action > File System > Create file.

In this example, the file content is the parsed object from the previous step.

This is the final structure for my flow:

Save it and run. You’ll get the action that is executed and the output written on a file that you can query with Power BI. If the API requires a parameter, then Power Automate is asking you to pass the parameter before running. This is shown in the below picture:

This was just an example. Think about all the actions you can perform through the combination of Power Automate and API call.

Conclusion

In this article, I showed you how to create a custom connector in Power Automate to invoke API calls and unleash the power of automation for all the repetitive tasks that need to be executed frequently.

Take your time to review the Power BI APIs, test them, find the right one suitable for your needs, and embed it into an action in Power Automate to achieve automation and simplify your governance approach for Power BI.

Andrea Martorana Tusa
About the author

Andrea Martorana Tusa is a Business Intelligence Team Manager at Würth Phoenix, the IT and consulting company of the Würth-Group. An MVP in the Data Platform category, he has over 20 years of experience working with data and is focused on the entire BI stack: database development, data warehousing, data analysis, reporting, etc. 

Andrea is a frequent speaker at many events such as SQLSaturdays, conferences in Europe and PASS Summit, including PASS Virtual Groups. He is also an author for sqlshack.com, sqlservercentral.com, and UGISS (User Group Italiano SQL Server).

2 comments on article "Leverage Power BI Automation Using Power Automate and API"

5/24/2020 8:49 AM
Arun Anthony

Thanks for this wonderful explanation! I have tried to create custom connection and at the end i dont see the connector to test and the field is blank. I have reverified your step and still doesn't show anything, i tried to click on new connection from that place and it shows me error for "AADSTS50011: The reply URL specified in the request does not match the reply URLs configured for the application". Have verified app registration and the URL's are same as you have mentioned. Please let me know if you have any idea



6/1/2020 3:48 PM
Alex Rostan

Arun, you must change the reply URL in the PowerBI app registration from https://www.powerbi.com to https://global.consent.azure-apim.net/redirect


Please login or register to post comments.

Theme picker

Back to Top
cage-aids
cage-aids
cage-aids
cage-aids