Power BI's REST APIs facilitate integration, administration, and governance. By providing programmatic access to datasets and reports, these APIs allow you to go beyond the standard functionality of the Microsoft Business Intelligence platform. Explore how these tools can help you push the limits of what Power BI can do. You can use the Power BI REST API to integrate your current systems, automate various tasks, and easily embed reports into custom applications to improve reporting efficiency. In this article, we'll discover how to integrate APIs into Power BI, how to call a Power BI REST API from Python, and what's the difference between Power BI and REST API.
An API, or Application Programming Interface, acts as a software intermediary that allows two applications to communicate with each other. Essentially, it facilitates the exchange and retrieval of information without requiring users to interact with the underlying technical details.
The acronym REST stands for Representational State Transfer. Consequently, a REST API is a programming interface that uses the concept of 'representative state transfer'. In practice, it allows you to retrieve elements of a service by sending a request.
These APIs are intended to influence the development and architecture of the World Wide Web. They establish a series of constraints that determine how distributed systems should behave.
REST APIs provide a flexible and lightweight method for integrating applications. By carrying out a search, you get results directly from the service where the request was initiated.
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.
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.
Dev4Side Software specializes in creating customized reports through Microsoft Power BI, transforming complex data into intuitive dashboards and reports to support informed business decisions.
Operating vertically across the entire Microsoft Power Platform ecosystem, we have developed excellent expertise in Power BI, allowing us to offer business intelligence solutions that highlight critical insights and promote operational efficiency.
Find out how we can help you make the most of your data. Contact us to transform business information into concrete actions.
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:
It includes a wide range of functionality across 17 distinct operating groups, such as administration, applications, datasets, dataflows, and reports.
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.
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.
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:
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.
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:
The response will include the access token. Copy the value of `access_token` from the response.
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}
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:
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.
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.
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:
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.
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.
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.
from requests_ntlm2 import HttpNtlmAuth
import requests
import os
import pandas as pd
# please change username, password and localhost
username = "username"
password = 'password'
localhost = "localhost"
# baseurl
baseurl = "http://{}/Reports/api/v2.0".format(localhost)
# Auth
auth = HttpNtlmAuth(username=username, password=password)
# example
result = requests.get(os.path.join(baseurl, "PowerBIReports"), auth=auth).json()
print(result)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.
reports_json = requests.get(os.path.join(baseurl, "PowerBIReports"), auth=auth).json()
reports_df = pd.DataFrame(reports_json['value'])
print(reports_df.head())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.
folders_json = requests.get(os.path.join(baseurl, "Folders"), auth=auth).json()
folders_df = pd.DataFrame(folders_json['value'])
print(folders_df.head())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.
def get_report_CacheRefreshPlans(tag):
result = requests.get(os.path.join(baseurl, tag), auth=auth).json()
reports = result['value']
cache_refresh_plans = [requests.get(os.path.join(baseurl, "PowerBIReports({})/CacheRefreshPlans".format(report['Id'])), auth=auth).json()['value'] for report in reports]
df = pd.concat([pd.DataFrame(plans) for plans in cache_refresh_plans])
return df
df_refresh_plans = get_report_CacheRefreshPlans("PowerBIReports")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.
def get_Policies_for_all_reports(tag):
result = requests.get(os.path.join(baseurl, tag), auth=auth).json()
reports_df = pd.DataFrame(result['value'])
policies = []
for report_id in reports_df['Id']:
baseurl_report = "{}/PowerBIReports({})".format(baseurl, report_id)
report_result = requests.get(os.path.join(baseurl_report, 'Policies'), auth=auth).json()
report_policies_df = pd.DataFrame(report_result['Policies'])
report_policies_df['ReportID'] = report_id
report_policies_df = report_policies_df[['ReportID', 'GroupUserName', 'Roles']]
policies.append(report_policies_df)
all_policies_df = pd.concat(policies, axis=0)
return all_policies_df
df_users = get_Policies_for_all_reports("PowerBIReports")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.
def get_df_from_pbrs(tag):
result = requests.get(os.path.join(baseurl, tag), auth=auth).json()
df = pd.DataFrame(result['value'])
return df
def get_report_related_data(tag, endpoint):
re = []
for i in range(len(get_df_from_pbrs(tag))):
id = get_df_from_pbrs(tag)['Id'][i]
url = "{}/PowerBIReports({})".format(baseurl, id)
result = requests.get(os.path.join(url, endpoint), auth=auth).json()
df_row = pd.DataFrame(result['value'])
df_row['ReportID'] = id
if endpoint == 'DataModelRoleAssignments' and len(df_row) != 0:
df_row['DataModelRoles'] = df_row['DataModelRoles'].apply(lambda x: x[0])
re.append(df_row)
df = pd.concat(re, axis=0)
return df
df_DataModelRoles = get_report_related_data(tag="PowerBIReports", endpoint="DataModelRoles")
df_DataModelRoleAssignments = get_report_related_data(tag="PowerBIReports", endpoint="DataModelRoleAssignments")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:
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:
It is not compatible with primary service profiles.
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.
Here are the answers to some of the most frequently asked questions about Power BI REST APIs, based on the content of our article.
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.
You can manage Power BI content, perform administrative tasks, integrate Power BI content into custom applications, and automate processes.
To use Power BI REST APIs, register an application in Azure Active Directory, obtain an access token, and use it to authenticate API requests.
Yes, Power BI can call a REST API directly using the 'Web' data source in Power BI Desktop.
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.
The Modern Apps team specializes in development and integration across the entire Microsoft 365 ecosystem. We design native applications for Microsoft and Azure platforms, and implement business processes that integrate with and maximize the investment in Microsoft 365.
