Refreshing large datasets can be very time consuming and not so end user friendly. Here is a walkthrough how one table can be refreshed.
Overview
Power bi provides a very powerful api that can do a lot of things. It opens up a world of possibilities for developers, enabling them to extend, automate, and integrate Power BI functionalities into their applications and workflows. In this blog we will look into how to refresh datasets, tables and how the authentication will work. Get more information about Power bi from here
Access and authentication
When integrating Power BI with your applications or services, granting permissions to a service principal is a critical step to ensure seamless access to Power BI datasets via the API. Will walk you through the process of setting up the necessary permissions for a service principal, detailing the rights it requires to interact with Power BI datasets effectively.
Prerequisites:
Before proceeding, make sure you have the following:
Azure Active Directory (AAD) Tenant:
Ensure you have an Azure AD tenant associated with your Power BI account. Registered Application and Service Principal:
Register an application in Azure AD, which automatically creates a corresponding service principal.
Power BI Account:
Make sure you have a Power BI account with the necessary permissions. Granting Permissions:
Step 1: Register an Application in Azure AD
Navigate to the Azure Portal (https://portal.azure.com/). In the left sidebar, go to Azure Active Directory > App registrations > New registration. Fill in the required details and click Register.
Step 2: Create a Service Principal
In the registered application, go to Certificates & secrets or Authentication to generate a client secret or certificate. Save the client secret or certificate thumbprint for later use.
Step 3: Grant API Permissions
In the registered application, go to API permissions > Add a permission > APIs my organization uses > Power BI Service API. Select the required permissions based on your use case: Dataset Permissions: Select Dataset > Dataset > Dataset.ReadWrite.All for read and write access to datasets. Workspace Permissions: Select Workspace > Workspace > Workspace.ReadWrite.All for read and write access to workspaces.
Step 4: Admin Consent
In the API permissions section, click Grant admin consent for [Your Organization].
Step 5: Obtain Tenant ID, Client ID, and Client Secret
In the registered application, note down the Tenant ID. Note down the Application (client) ID as the Client ID. Use the client secret details as the Client Secret.
Now that you have can access Power bi APi let’s get the bearer token
import requests
import json
client_credentials = ""
client_id =""
tenant_id =""
# Set the request payload
payload = {
"grant_type": "client_credentials",
"client_id": client_id,
"client_secret": client_secret,
"resource": "https://analysis.windows.net/powerbi/api"
}
# Set the request headers
headers = {
"Content-Type": "application/x-www-form-urlencoded"
}
access_token = ''
# Send the request to the Azure AD token endpoint
response = requests.post("https://login.microsoftonline.com/{tenant_id}/oauth2/token", data=payload, headers=headers)
# Check the status code of the response
if response.status_code == 200:
# Print the access token
access_token = response.json()["access_token"]
#print(access_token)
else:
# Print an error message
print(f"Error: {response.status_code}")
Now that you have the bearer token, lets refresh a single table in a dataset
import requests
import json
# Replace with your actual dataset ID
datasets = ""
table = ""
workspace_id = ""
# Replace with the actual URL
url = "https://api.powerbi.com/v1.0/myorg/groups/{}/datasets/{}/refreshes".format(workspace_id, datasets)
# Set the headers with the access token and content type
headers = {
"Authorization": f"Bearer {access_token}", #bearer token used here
"Content-Type": "application/json"
}
# Define the JSON body
json_body = {
"type": "full",
"commitMode": "transactional",
"objects": [
{ # table that you want to refresh
"table": table
}
],
"applyRefreshPolicy": "false"
}
# Convert the JSON body to a string
json_body_str = json.dumps(json_body)
# Make the POST request with the JSON body
response = requests.post(url, headers=headers, data=json_body_str)
# Check the response status code
if response.status_code == 202:
print("Queries executed successfully.")
else:
print("Error executing queries:")
print("Status Code:", response.status_code)
print("Response:", response.text)
If you want to refresh a single partition then add partition under the table like this
{
"table": "",
"partition": ""
},
Summary
This can be used for any case that you might need to refresh a single table and not the full dataset.