How to Get Data from a Power BI Dataset via an API

How to Get Data from a Power BI Dataset via an API

The Power BI REST API lets you query dataset data programmatically using DAX queries sent over HTTPS. You authenticate via Azure AD (OAuth 2.0), call the Execute Queries endpoint, and receive JSON results. This guide covers authentication setup, the DAX query format, API limits, and Python/PowerShell examples.

Key facts

• The key endpoint: POST https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/executeQueries
• Authentication: Azure AD OAuth 2.0 token. Scopes: https://analysis.windows.net/powerbi/api/.default
• API limits: 100,000 rows per result, 120-second query timeout, one EVALUATE per request.
• Requires a Power BI Pro or Premium Per User (PPU) license for the calling account.
• Supports DAX queries only — not SQL. Your query goes inside an EVALUATE statement.
• EPC Group: 29 years of Microsoft consulting, 1,500+ Power BI deployments, Microsoft Solutions Partner.

Step 1 — Register an Azure AD app

Before calling the API, register an app in Azure AD to get client credentials.

1. Open the Azure portal → Azure Active Directory → App registrations → New registration.
2. Name the app (e.g., "PowerBI-API-Client") and set the redirect URI for your client type.
3. Under API permissions, add the Power BI Service permission: Dataset.ReadWrite.All or Dataset.Read.All.
4. Grant admin consent for the permission.
5. Under Certificates & secrets, create a client secret. Copy the secret value immediately — it only shows once.
6. Note your Application (client) ID and Directory (tenant) ID from the Overview page.

Step 2 — Get an access token

Use the OAuth 2.0 client credentials flow to get a bearer token.

Python example

import requests

tenant_id = "your-tenant-id"
client_id = "your-client-id"
client_secret = "your-client-secret"

token_url = f"https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token"
token_data = {
    "grant_type": "client_credentials",
    "client_id": client_id,
    "client_secret": client_secret,
    "scope": "https://analysis.windows.net/powerbi/api/.default"
}

response = requests.post(token_url, data=token_data)
access_token = response.json()["access_token"]

PowerShell example

$body = @{
    grant_type    = "client_credentials"
    client_id     = "your-client-id"
    client_secret = "your-client-secret"
    scope         = "https://analysis.windows.net/powerbi/api/.default"
}
$token = Invoke-RestMethod -Method Post `
  -Uri "https://login.microsoftonline.com/your-tenant-id/oauth2/v2.0/token" `
  -Body $body
$accessToken = $token.access_token

Step 3 — Find your dataset ID

You need the dataset ID to call the Execute Queries endpoint.

1. In Power BI Service, open the workspace containing your dataset.
2. Click the dataset name → Settings.
3. The URL contains the dataset ID: .../datasets/{datasetId}/settings. Copy that GUID.

Alternatively, call the GET /v1.0/myorg/datasets endpoint to list all datasets and find the ID programmatically.

Step 4 — Execute a DAX query

Send a POST request with your DAX query in the request body.

Python example

dataset_id = "your-dataset-id"
headers = {
    "Authorization": f"Bearer {access_token}",
    "Content-Type": "application/json"
}
payload = {
    "queries": [
        {
            "query": "EVALUATE SUMMARIZE(Sales, Sales[Region], \"Total\", SUM(Sales[Amount]))"
        }
    ],
    "serializerSettings": {
        "includeNulls": True
    }
}
url = f"https://api.powerbi.com/v1.0/myorg/datasets/{dataset_id}/executeQueries"
response = requests.post(url, headers=headers, json=payload)
data = response.json()

The response returns a JSON object with a results array. Each result contains a tables array with rows.

API limits and how to work around them

• 100,000 row limit — use TOPN or WHERE filters in your DAX query. Paginate by filtering on a surrogate key.
• 120-second timeout — optimize your DAX query. Add summary tables or aggregations to the model if queries are slow.
• One EVALUATE per request — for multiple queries, make separate API calls or use UNION to combine results in a single EVALUATE.
• Rate limits — the API enforces per-workspace call limits. Use exponential backoff for retry logic in production pipelines.

XMLA endpoint: an alternative for larger datasets

For Premium or Fabric-licensed workspaces, use the XMLA endpoint instead of the REST API for larger query volumes.

• The XMLA endpoint accepts MDX and DAX queries via Analysis Services client libraries.
• No 100,000 row limit at the API layer — limits depend on dataset capacity.
• Connect with SQL Server Management Studio (SSMS), Tabular Editor, or Python's adodbapi library.
• XMLA read access requires a Power BI Premium Per User (PPU) or Premium capacity workspace.

Frequently asked questions

Do I need Power BI Premium to use the Execute Queries API?

No. The Execute Queries endpoint works with Pro and PPU licenses. Premium is not required. However, the XMLA endpoint — a more powerful alternative — does require Premium or Fabric capacity.

Can I use SQL instead of DAX to query a Power BI dataset?

Not through the REST API. The Execute Queries endpoint accepts DAX only. If you need SQL, connect through the XMLA endpoint using a DAX or MDX client, or query the underlying source database directly.

Can a service principal call the Power BI API?

Yes. Register an Azure AD app, grant it Dataset.Read.All permission, and add the service principal to the Power BI workspace as a member or admin. Enable service principal access in the Power BI admin portal under Tenant settings.

How do I handle the 100,000 row limit?

Filter your DAX query to return only the rows you need for each call. For large exports, paginate by filtering on an ID column: first call returns rows 1–100,000, second call returns rows 100,001–200,000, and so on.

Is there a way to stream real-time data from Power BI via the API?

Yes, but through a different API: the Push Datasets API. You push data into Power BI in real time from your system. For reading data out of Power BI in real time, use the XMLA endpoint with a streaming dataset or query the underlying source system directly.

Talk to a Power BI integration architect

EPC Group architects design Power BI API integrations, data pipelines, and embedded analytics for enterprise clients. Call (888) 381-9725 or request a 30-minute discovery call.