Service Principal Approach to Extract Data from Power BI Semantic Models via Python

Power BI's REST API allows secure, programmatic access to datasets—also known as semantic models—enabling automation, integration, and advanced analytics. In enterprise environments, Service Principal authentication is the preferred method because it doesn't require a user to be present, supports automation, and follows best practices for security and governance.

In this guide, you'll learn how to use Python, MSAL, and a service principal to authenticate and query a Power BI semantic model.

🚀Why Use a Service Principal?

• ✅No dependency on a user login

• ✅Perfect for automation or backend jobs

• ✅Fine-grained control over access using Azure AD roles and Power BI workspace membership

• ✅Better auditability and governance

🔧Prerequisites

You’ll need:

• 🔐An Azure AD registered application (Service Principal)

• 🧾 Tenant ID, Client ID, and Client Secret

• 📊Power BI workspace with a semantic model (dataset)

• 🛡️ Admin access to configure permissions

• 🧾Install MSAL

pip install msal requests

🛠️ Step-by-Step Setup

1. Register the App in Entra ID

1. Go to Entra Admin Center

2. Navigate to App registrations > New registration

3. Record the Application (client) ID and Directory (tenant) ID

2. Create a Client Secret

1. Under Certificates & Secrets, add a New client secret

2. Save it securely—you won't be able to view it again later

3. Grant API Permissions

Go to API Permissions > Add a permission:

• Power BI Service

  • Dataset.Read.All OR Dataset.ReadWrite.All

🔔Don’t forget to grant admin consent for these permissions.

4. Enable Service Principal Access in Power BI Admin Portal

1. Go to Power BI > Admin Portal > Tenant settings

2. Scroll to Developer settings

3. Enable "Service principals can use Power BI APIs"

5. Add Service Principal to Workspace

1. Go to the Power BI workspace

2. Click Manage access

3. Add your service principal (the app’s name) as a Viewer or Member

Once everything is set use the following Python code to extract the data 

🧠 Python Code Example

import requests
from msal import ConfidentialClientApplication
import json

# === Configuration ===
TENANT_ID = 'your-tenant-id'
CLIENT_ID = 'your-client-id'
CLIENT_SECRET = 'your-client-secret'
WORKSPACE_ID = 'your-workspace-id'
DATASET_ID = 'your-dataset-id'

# === Authentication ===
AUTHORITY = f'https://login.microsoftonline.com/{TENANT_ID}'
SCOPE = ['https://analysis.windows.net/powerbi/api/.default']

app = ConfidentialClientApplication(
    CLIENT_ID,
    authority=AUTHORITY,
    client_credential=CLIENT_SECRET
)

result = app.acquire_token_for_client(scopes=SCOPE)
if "access_token" not in result:
    raise Exception(f"Token acquisition failed: {result.get('error_description')}")

# === Run DAX Query ===
url = f"https://api.powerbi.com/v1.0/myorg/groups/{WORKSPACE_ID}/datasets/{DATASET_ID}/executeQueries"

headers = {
    'Authorization': f"Bearer {result['access_token']}",
    'Content-Type': 'application/json'
}

payload = {
    "queries": [
        {
            "query": "EVALUATE VALUES(YourTable)"
        }
    ],
    "serializerSettings": {
        "includeNulls": True
    }
}

response = requests.post(url, headers=headers, json=payload)

if response.status_code == 200:
    print(json.dumps(response.json(), indent=2))
else:
    print(f"Error {response.status_code}: {response.text}")

Expected output 

📌Final Thoughts

The service principal-based approach is the most secure and scalable way to interact with Power BI datasets. It enables backend systems, automation workflows, and enterprise-grade solutions to query data without manual intervention.