Using Power BI REST API to Push Data from JSON Files into Power BI Datasets

Welcome to our deep dive into using the Power BI REST API to push data from JSON files directly into a Power BI dataset. This tutorial is designed to guide even those new to APIs and Power BI through the process step-by-step, ensuring you can implement these techniques effortlessly.

What is an API?

API stands for Application Programming Interface. It’s a set of rules that allows programs to interact with each other. Developers use APIs to enable software applications to communicate with one another, accessing features or data without needing to understand how those systems work internally.

What is the Power BI REST API?

The Power BI REST API provides service endpoints for embedding, user resources, and administration. It allows developers to push data updates into datasets in Power BI, creating and managing dashboards, reports, and much more programmatically. This is particularly useful for automating the updates to your Power BI reports and integrating Power BI capabilities into custom applications.

Pushing Data from a JSON File into a Power BI Dataset

Step 1: Register an Application in Azure

Before you can interact with the Power BI REST API, you need to register your application in Microsoft Azure to authenticate API requests.

1. Go to the Azure Portal: Log in to your Microsoft Azure account.
2. Navigate to Azure Active Directory > App registrations > New registration.
3. Register your application: Enter a name for your app, select who can use the application, and then specify the redirect URI (the URL where users will be redirected after being authenticated).
4. Obtain Application ID and Tenant ID: After registration, note down the Application (client) ID and Directory (tenant) ID from the overview page.
5. Create a client secret: Under “Certificates & secrets”, create a new client secret. Store the value securely as it won’t be shown again.

Step 2: Get Required Permissions

1. API permissions: Go to “API permissions” in your app’s registration in Azure and add permissions. For pushing data, select Dataset.ReadWrite.All under Power BI Service.

Step 3: Get Authentication Token

The Power BI API uses OAuth 2.0 for authentication. Here’s how to get an access token using your application credentials.

1. Construct a POST request to the Microsoft identity platform token endpoint:

POST https://login.microsoftonline.com/{tenant}/oauth2/v2.0/token
Content-Type: application/x-www-form-urlencoded

client_id={client_id}&scope=https://graph.microsoft.com/.default&client_secret={client_secret}&grant_type=client_credentials

Replace {tenant}, {client_id}, and {client_secret} with your specific details.

Step 4: Push Data into Power BI Dataset

Assuming you have a dataset in Power BI with a table ready to accept the JSON data:

1. Prepare your JSON data: This is the data you intend to push to Power BI. Ensure it matches the schema of the dataset’s table.
2. Make a POST request to push data:

Replace {dataset_id}, {table_name}, and {access_token} with your actual dataset ID, table name, and the OAuth token obtained earlier.

Step 5: Automate the Process

You can automate this process using a script that reads data from a JSON file and uses the above API call to push data into Power BI. This can be scheduled as a batch job or run as needed.

Let’s walk through a practical example of using the Power BI REST API to push data from a JSON file into a Power BI dataset. We’ll assume you’ve already followed the previous steps to register an application in Azure, obtained necessary permissions, and have the authentication token. Now, we’ll demonstrate how to use this setup to push data into a dataset’s table in Power BI.

Example Scenario

Suppose you have a Power BI dataset named SalesData, and it contains a table called DailySales. This table has the following schema:

• Date: A date representing when the sales occurred.
• SalesAmount: A numerical field representing the total sales amount.
• ProductID: An identifier for the product sold.

You have a JSON file containing new sales data that you want to upload to this table. Here’s what the JSON might look like:

{
  "rows": [
    { "Date": "2024-05-04", "SalesAmount": 1500, "ProductID": 101 },
    { "Date": "2024-05-04", "SalesAmount": 2700, "ProductID": 102 }
  ]
}

Steps to Push Data Using Power BI REST API

1. Prepare Your Authentication and Headers

First, ensure that you have your OAuth 2.0 access token, which is required for authentication. You’ll also need to set up the correct headers for your HTTP request:

import requests  # Import requests library to make HTTP requests

url = "https://api.powerbi.com/v1.0/myorg/datasets/{dataset_id}/tables/DailySales/rows"
headers = {
    "Content-Type": "application/json",
    "Authorization": "Bearer {your_access_token}"
}

Replace {dataset_id} with the ID of your dataset, and {your_access_token} with your actual OAuth token.

2. Load Your JSON Data

You need to load your JSON data from the file (or it could be directly within your script if it’s dynamically generated). Here’s how you might load it if it’s stored in a file:

import json

# Load JSON data from a file
with open('data.json', 'r') as file:
    data = json.load(file)

3. Make the POST Request to Push Data

Now, send a POST request to Power BI to add the data to your DailySales table:

response = requests.post(url, headers=headers, json=data)
print(response.status_code)  # Print the status code to see if it succeeded (200 OK or 201 Created)

4. Handling the Response

Check the response from the Power BI API to ensure that the data was pushed successfully:

if response.status_code == 200 or response.status_code == 201:
    print("Data pushed successfully!")
else:
    print(f"Failed to push data: {response.text}")

Conclusion

This example illustrates how to use the Power BI REST API to push data from a JSON file into a Power BI dataset. By automating this process, you can continuously update your datasets and dashboards with new data, leveraging Power BI’s dynamic reporting capabilities.