Power BI REST API: What are they and how to use them?

Power BI REST APIs: What are they?

Power BI REST APIs are Microsoft’s programmatic interfaces for managing and embedding Power BI resources — reports, dashboards, datasets, workspaces, and dataflows — from external applications. Use cases include automated report publishing via CI/CD pipelines, dynamic row-level security token generation for embedded analytics, workspace provisioning scripts, and usage metrics extraction for governance dashboards.

A REST API consists of several components. Among these, the ‘headers’ specify the authentication method and the types of content.

To call a REST API, you use a ‘method’. While there are several methods available, the most commonly used ones are POST and GET. The GET method retrieves the information, while the POST method sends information to web resources.

The “Endpoint” is the URL of the resource that processes requests. Meanwhile, ‘Data’ or ‘Body’ refers to the textual content in JSON format transmitted to the resource.

To ensure that a REST API works effectively, several design principles must be followed. First, server and client applications must be completely decoupled and operate independently. On the client side, the only accessible information should be the URL of the requested resource. The server, in turn, is responsible for sending only the requested data to the client using HTTP.

Another important feature of a REST API is that it is stateless. This means that each request must include all the information necessary for processing, eliminating the need for server-side sessions. The server is not authorized to store any data related to client requests.

Resources should be cached whenever possible, both server-side and client-side. In addition, server responses must include details about whether or not to allow caching of the provided resource.

The interface should maintain uniformity, meaning that requests for the same resource must be consistent regardless of where they originate. It is also essential that calls and responses go through multiple intermediate levels.

To securely access the Power BI REST APIs, you need to start with an integrated token request. This request allows Power BI API calls and enables the execution of various functions.

What’s the difference between Power BI and REST API

The main difference is that Power BI is the platform for visualizing and analyzing data, while REST APIs provide the interface to empower, automate, and integrate these capabilities into a larger and more dynamic ecosystem.

Power BI is a collection of business intelligence tools developed by Microsoft. It allows users to visualize data through interactive dashboards and reports, perform advanced analysis, share insights with team members via the cloud, and connect to a wide range of data sources, including databases, Excel files, and online services.

Essentially, Power BI offers a cohesive environment for transforming raw data into representations. REST APIs, or Application Programming Interfaces built on Representational State Transfer, serve as a means of making different applications interact using the HTTP protocol. These APIs allow developers to use the functionality of a service through HTTP requests (GET, POST, PUT, DELETE), facilitating the integration of various systems in a standardized and scalable way, while automating processes that would otherwise require manual intervention.

In the context of Power BI, the Power BI REST APIs allow developers to programmatically interact with Power BI services.

Power BI REST API: what are they for and types of operations

The Power BI REST API provides access to various service endpoints, allowing you to perform all the operations supported by the Power BI service.

You can create a custom application using the Power BI REST API that allows users to access, update, or delete datasets. In addition, by taking advantage of other Power BI API endpoints, you can introduce different features that give users complete control over the platform.

In summary, the Power BI API allows you to:

• Manage Power BI content. Perform tasks such as adding, updating, and removing datasets, reports, dashboards, and workspaces.‍
• Perform administrative tasks. Manage users by adding or removing them and overseeing their roles, access levels, and permissions.‍
• Integrate Power BI content. Embed Power BI charts into custom web applications and present real-time visualizations.‍ It includes a wide range of functionality across 17 distinct operating groups, such as administration, applications, datasets, dataflows, and reports.

Operazioni delle REST API di Power BI

           Operation group        Description                Admin        Operations for working with administrative tasks.                Apps        Operations for working with Apps.                Available Features        Operations that return available features.                Capacities        Operations for working with capacities.                Dashboards        Operations for working with dashboards.                Dataflow Storage Accounts        Operations for working with dataflow storage accounts.                Dataflows        Operations for working with dataflows.                Datasets        Operations for working with datasets.                Embed Token        Operations for working with embed tokens.                Gateways        Operations for working with gateways.                Groups        Operations for working with groups.                Imports        Operations for working with imports.                Pipelines        Operations for working with deployment pipelines.                Push Datasets        Operations for working with push datasets.                Reports        Operations for working with reports.                Template Apps        Operations for working with Template Apps.                Users        Operations for working with users.    

Power BI REST API: How to use them?

To interact with the Power BI REST API, you must obtain an application access token for authorization. This token works like a credential, similar to a password, that allows access to protected resources.

First, you must register an application in Azure Active Directory (Microsoft Sign In) to obtain the access token.

To obtain the Power BI access token, it is necessary to register an app for Power BI, authorize the user through Application ID, and exchange the authorization code for the access token.

Only now can you use the access token to authorize the Power BI request and call the Power BI API.

Below we analyze all the steps mentioned above in detail.

Register an app for Power BI

To access the Power BI REST API, you must register an application in Azure AD (Microsoft Enter ID). This configuration defines the permissions needed to use the API. Visit the Power BI app registration tool and sign in with your Power BI account.

Choose a name for your application and set its type to ‘Server-side Web Application’. Specify the URL of your app’s home page and the redirect URL where users will be directed with the authorization code.

Scroll down to select the APIs that your application is authorized to use. You can set the access level for these APIs now, with the option to change them later in Azure. Click ‘Register’ to save your application.

After successfully registering the application, you will receive the Application ID and Application Secret. Copy and keep these details securely, as you’ll use them later to use the Power BI REST API.

Authorize the user using Application ID

After registering the app, log in to get your access token. This token will be needed to call the Power BI REST API in subsequent steps. To do this, log in to https://login.microsoftonline.com/common/oauth2/authorize with the following parameters:

• ClientID
• ResponseType
• Redirects
• ResponseMode
• Scope
• State (optional)‍ Build the URL and enter it in your browser’s address bar, then press Enter. Click ‘Accept’ to grant the required permissions to the app.

You will be redirected to the specified URL with the included authorization code. Find the authorization code in the URL, which appears after code= and before &, and copy this segment.

That segment is the authorization code. You may receive a ‘page not found’ error right now, as you may not have configured an active app at the redirect URL to handle the authorization request.

Get Power BI API access token

Once you have obtained the authorization code, you can exchange it for an access token. To retrieve the token, send a POST request to the token endpoint with the specified code and details:

• Client ID
• Client Secret
• Code
• Scope
• Redirect URI
• Grant Type‍ The response will include the access token. Copy the value of access_token from the response.

Call Power BI API

After obtaining the access token, you can use it to authenticate your Power BI API requests. Include the following in the request header:

Bearer {access_token}

Can Power BI call a REST API?

You can call a REST API directly from Power BI. The ‘Web’ data source in Power BI Desktop allows you to retrieve data from any endpoint of a web API. You’ll need to enter the JSON URL and include the access token if necessary.

Let’s say you want to retrieve user data from a web API endpoint. Here’s how to proceed.

In Power BI Desktop, go to Home > Data and click Get Data. From the drop-down menu, choose Web. Enter the URL of the web API endpoint and click OK. You can opt for the advanced option to include specific parameters and headers, if necessary.

Depending on the type of authentication required, follow these steps:

• Anonymous: Use this option for public APIs that don’t require authentication.‍
• Basic: Authenticate the request by providing a username and password.‍
• For a web API, enter the access key to authorize your API request. In this case, we’re using a public API that doesn’t require authentication. Click Connect.‍
• You can modify the results in the Power Query editor, if necessary; then, click Close & Apply to finalize your changes.‍ And that’s all! The information from the REST API will be successfully loaded into Power BI. You can use them to generate visual reports, as shown below.

Power BI REST API: import data and update dashboard

A REST API can serve as a source for importing data into Power BI from various services. In addition, you can set up automatic updates for your Power BI dashboard by scheduling data refreshes.

To get started, open the Power BI Desktop app and click the ‘Get Data’ button. From there, select the ‘Web’ option and enter the URL of the REST API endpoint from which you want to retrieve data.

Data imported from a REST API usually arrives in JSON format, making it essential to convert them to tabular format for effective modeling and visualization.

To achieve this, right click on the dataset in the Power Query editor and select Convert to Table. This action will reveal a button that allows you to expand the JSON attributes, allowing you to select which attributes you want to incorporate into your data model.

After creating visualizations in your report using the imported data models, you can build a dashboard to share these visualizations by publishing them in the online version of Power BI.

In addition, you can set up an automatic update for the dataset. In the online interface, right click on the dataset and select the Schedule Update option. This allows Power BI to periodically retrieve new data from the REST API endpoint, ensuring that your dashboard views are always up to date.

Power BI API: Service Principal

To allow Azure applications to access the Power BI API, you can configure a service principal. This acts as a security identity for the application, eliminating the need for manual authentication methods.

Here’s how to set up a service principal for the Power BI REST API:

• Start by creating a new application in Azure AD (Enter ID) and setting up a security group. Next, grant service principals access to the Power BI API.‍
• Log in to the Power BI Admin Portal, select Tenant Settings and, under Developer Settings, activate the option ‘Embed content in apps’.‍
• Activate the option ‘Allow service principals to use the Power BI APIs in the same section and click ‘Apply’.‍ It’s important to note that the Azure AD app can only access Power BI reports, dashboards, and datasets if it has the appropriate permissions for the workspace.

To include a service account in your Workspace, start by signing in to the Power BI Service.

From there, select the ‘More’ menu and then choose ‘Access to Workspace’ for the relevant workspace. In the ‘Add Administrators, Members, or Collaborators’ section, add the Service Principal and click ‘Add’. The name of the service principal matches the display name of the Azure AD app.

You have successfully configured a service principal and are ready to use the Power BI REST API with it.

How to call the Power BI REST API from Phyton

The REST API offers advanced functionality, such as browsing the folder hierarchy, discovering folder contents, downloading report definitions, modifying predefined report parameters, and managing upgrade plans, among other capabilities. All of these Power BI APIs can be called from Python; here’s how to do it.

Power BI REST API from Phyton: Report Server

You can use this instance in API calls to authenticate with the Power BI Report Server and access various API endpoints. The following code example illustrates how to make an API request in Python to obtain the list of Power BI reports hosted on the server.

To authenticate with the Power BI Report Server API, you’ll need to use the requests_ntlm2 library along with the HttpNtlmAuth method for NTLM authentication. First, define the username, password, and baseurl variables. Then, create an instance of HttpNtlmAuth using these credentials to establish the connection.

Phyton’s Power BI REST API: List of Reports

The code retrieves a list of Power BI reports from the Power BI Report Server by accessing the PowerBIReports endpoint. It sends a GET request to the designated endpoint, authenticating with the credentials provided. The resulting response is then converted into a Pandas DataFrame for easier management and analysis.

Phyton’s Power BI REST API: List of Folders

Here’s a snippet of Python code to get the list of folders from the Power BI Report Server. This code uses the requests library to send a GET request to the relevant endpoint and processes the JSON response to generate a Pandas DataFrame, containing the details of the folders available on the server.

Phyton’s Power BI REST API: List of Refresh Plan

To obtain the CacheRefreshPlans for all Power BI reports published on the Power BI Report Server, you must first collect the details of all the reports. Next, you need to iterate through each report to retrieve the related cacheRefreshPlans.

This is achieved first by retrieving the full list of Power BI reports and then accessing the CacheRefreshPlans for each report individually. By combining the results of each report, you can create a complete DataFrame that provides an overview of the refresh plans for all Power BI reports.

Phyton’s Power BI REST API: List of Users

To get the list of users connected to Power BI reports on the Power BI Report Server, you can use the following Python code. This code interacts with the Power BI REST API to retrieve item policies for each report and extract relevant user information.

Once you run the code, you’ll have a DataFrame that includes report IDs, group user names, and user roles linked to each report. This information can be used to manage and analyze user access policies and permissions for Power BI reports on the server.

Phyton’s Power BI REST API: List of RLS

The code uses the Power BI Report Server API to collect row-level security information from Power BI reports. It includes two main functions: get_df_from_pbrs, which retrieves a report DataFrame, and get_report_related_data, which obtains the data model roles and role assignments for each report.

The resulting DataFrames provide information about row-level security settings in Power BI reports on the Power BI Report Server. You can easily obtain this information by calling functions with the relevant parameters.

Power BI REST API: What are the limitations?

Microsoft Power BI implements a throttling system to improve performance and prevent API abuse. Time-based limits are imposed on the number of API requests and lines processed. Although not officially documented, here are some commonly observed frequency limits for the Power BI REST API:

• 120 POST requests per minute

• 10,000 lines per POST request

• 1 million lines per hour‍ If you exceed these limits in a certain amount of time, you may receive the 429 status code. In addition, the push dataset feature, which supports real-time updates, has its own specific restrictions, including:

• Maximum 75 rows and columns each

• Maximum 10,000 lines per POST request

• 1 million rows added per hour per dataset

• Maximum 5 pending POST requests per dataset

• 120 POST requests for rows per dataset per minute‍ It is not compatible with primary service profiles.

Conclusions

The Power BI REST APIs are essential for those who work with Power BI on a daily basis. These Power BI APIs allow you to automate, manage and integrate complex operations, eliminating the need for manual interventions and reducing the time spent.

Thanks to their ability to interface with existing systems, automatically update datasets, embed reports in customized applications, and manage permissions and roles, the Power BI REST APIs significantly expand the functionality of the Power BI platform, allowing you to make the most of the potential of the data.

FAQ on Power BI REST APIs

Here are the answers to some of the most frequently asked questions about Power BI REST APIs, based on the content of our article.

What are Power BI REST APIs?

Power BI REST APIs are programming interfaces that enable programmatic access to Power BI service functionalities like datasets, reports, and dashboards, allowing integration, automation, and embedding of Power BI capabilities into custom applications.

What can you do with Power BI REST APIs?

You can manage Power BI content, perform administrative tasks, integrate Power BI content into custom applications, and automate processes.

How do you use Power BI REST APIs?

To use Power BI REST APIs, register an application in Azure Active Directory, obtain an access token, and use it to authenticate API requests.

Can Power BI call a REST API?

Yes, Power BI can call a REST API directly using the ‘Web’ data source in Power BI Desktop.

What are the limitations of Power BI REST APIs?

Power BI REST APIs have limits such as 120 POST requests per minute, 10,000 lines per POST request, and 1 million lines per hour. Exceeding these limits may result in a 429 status code.