How to Get Data from a Power BI Dataset via an API
Accessing data from Power BI datasets programmatically via the REST API enables organizations to integrate BI data into custom applications, automate reporting workflows, and build data pipelines that extend Power BI's native capabilities. At EPC Group, we have built hundreds of API-driven integrations for enterprise clients who need to extract, transform, and consume Power BI data outside the standard Power BI Service interface.
Understanding the Power BI REST API
The Power BI REST API is a comprehensive set of endpoints that allow developers to interact programmatically with Power BI resources including datasets, reports, dashboards, workspaces, and more. For data extraction specifically, the most important endpoint is the Execute Queries API, which enables you to run DAX queries against a published dataset and receive results in JSON format.
The API is organized around Azure Active Directory (now Microsoft Entra ID) authentication, meaning all requests must include a valid OAuth 2.0 bearer token. This ensures enterprise-grade security and enables fine-grained access control through Azure AD app registrations, service principals, and row-level security (RLS) enforcement even at the API level.
Before diving into implementation, it is important to understand that the Power BI API is not designed as a high-volume data extraction tool. It is optimized for moderate query loads and interactive scenarios. For bulk data extraction, consider using Azure Synapse Link, XMLA endpoints, or exporting to Azure Data Lake Storage. Our consultants help organizations choose the right approach based on their data volume, latency requirements, and architecture.
Authentication: Setting Up Azure AD App Registration
The first step in accessing Power BI data via the API is setting up authentication through Microsoft Entra ID (Azure AD). You need to register an application in Azure AD and grant it the appropriate Power BI API permissions. There are two primary authentication approaches:
- Service Principal authentication - Recommended for production applications and automated pipelines. Create an Azure AD app registration, generate a client secret or certificate, and assign the service principal to Power BI workspaces. This approach does not require a user context and supports unattended execution.
- Delegated (user) authentication - Used when the API call should execute in the context of a specific user, inheriting their permissions and RLS assignments. This approach requires interactive login or a stored refresh token and is typically used in custom web applications where users authenticate individually.
For service principal authentication, you must enable service principal access in the Power BI admin portal under Tenant Settings. Navigate to Developer Settings and enable "Allow service principals to use Power BI APIs." You can scope this to specific security groups for tighter control. The service principal must also be added as a member or admin of the target workspace.
The required API permissions include Dataset.Read.All for reading dataset data and Dataset.ReadWrite.All if you also need to push data or modify datasets. For the Execute Queries endpoint specifically, your service principal or user must have Build permission on the target dataset.
Executing DAX Queries via the API
The Execute Queries endpoint (POST /datasets/{datasetId}/executeQueries) accepts a DAX query and returns the results as a JSON table. This is the primary method for extracting data from a published Power BI dataset. The request body contains the DAX query wrapped in a JSON structure:
{
"queries": [
{
"query": "EVALUATE TOPN(100, 'Sales', 'Sales'[Revenue], DESC)"
}
],
"serializerSettings": {
"includeNulls": true
}
}The DAX query must use the EVALUATE statement, which returns a table result. You can use any valid DAX table expression including SUMMARIZECOLUMNS, CALCULATETABLE, FILTER, TOPN, and ADDCOLUMNS. This gives you tremendous flexibility to shape the data exactly as your application needs it, applying filters, aggregations, and calculations server-side.
Key limitations to be aware of: the API enforces a maximum of 100,000 rows per query result, a 120-second query timeout, and a limit of one query per request (though you can batch multiple EVALUATE statements). For datasets larger than these limits, implement pagination using TOPN with offset patterns or use the XMLA endpoint instead.
The response includes metadata about each column (name and data type) and the row data in a structured array. Parse this JSON response in your application to populate data grids, feed dashboards, trigger automated alerts, or load data into downstream systems.
XMLA Endpoints for Advanced Data Access
For organizations with Power BI Premium or Premium Per User (PPU) licensing, XMLA endpoints provide a more powerful and familiar way to access dataset data. XMLA (XML for Analysis) is the same protocol used by SQL Server Analysis Services (SSAS), which means existing tools and libraries that connect to SSAS can connect directly to Power BI datasets.
XMLA endpoints support both read and read/write operations. With read access, you can use tools like SQL Server Management Studio (SSMS), Azure Data Studio, or any OLEDB/ADOMD.NET client library to query Power BI datasets using DAX or MDX. With read/write access, you can also process tables, manage partitions, and perform administrative operations programmatically.
The XMLA connection string follows the format: powerbi://api.powerbi.com/v1.0/myorg/WorkspaceName. This makes Power BI datasets accessible from virtually any analytics tool that supports Analysis Services connections, including Python (using the pyadomd library), R, and custom .NET applications.
Practical Implementation Patterns
At EPC Group, we see several common patterns for API-based data access from Power BI datasets:
- Custom application dashboards - Embedding specific KPIs from Power BI datasets into line-of-business applications, CRM systems, or internal portals using the Execute Queries API to fetch real-time metrics.
- Automated alerting and monitoring - Azure Functions or Logic Apps that periodically query Power BI datasets to check threshold conditions and trigger email, Teams, or SMS notifications when business rules are violated.
- Data export pipelines - Scheduled processes that extract data from Power BI datasets and load it into other systems such as data warehouses, reporting databases, or third-party analytics platforms.
- Self-service data portals - Custom web applications that allow business users to select parameters and receive formatted data exports (Excel, CSV, PDF) generated from Power BI dataset queries.
- Mobile and IoT integrations - Lightweight API calls from mobile apps or IoT dashboards that fetch aggregated metrics from centrally governed Power BI datasets rather than querying raw data sources directly.
Each pattern requires careful consideration of authentication, caching, rate limiting, and error handling. Our consultants design these integrations with production-grade reliability, implementing retry logic with exponential backoff, token caching to minimize authentication overhead, and proper error handling for API throttling (HTTP 429) responses.
Why Choose EPC Group for Power BI API Integration
With 28+ years of enterprise Microsoft consulting experience, EPC Group brings deep expertise in Power BI architecture, Azure AD integration, and custom application development. Our team has built API integrations for Fortune 500 companies, healthcare systems requiring HIPAA-compliant data handling, and financial institutions operating under SOC 2 controls.
We do not just connect to APIs. We architect solutions that are secure, scalable, and maintainable. This includes implementing service principal governance, building monitoring dashboards for API usage and performance, and designing fallback strategies for when the API is unavailable. As a Microsoft Gold Partner, we have direct access to Microsoft engineering resources when complex issues arise.
Need Power BI API Integration?
Contact EPC Group to discuss your Power BI API integration requirements. Whether you need to extract data for custom applications, build automated workflows, or connect Power BI to third-party platforms, our enterprise consultants deliver production-ready solutions.
Frequently Asked Questions
Do I need Power BI Premium to use the REST API?
No. The Execute Queries endpoint works with both Power BI Pro and Premium datasets. However, XMLA endpoint access requires Premium or Premium Per User licensing. The REST API also has more restrictive rate limits for Pro workspaces compared to Premium capacity. For high-volume API usage, Premium is recommended.
What programming languages can I use to call the Power BI API?
Any language that can make HTTP requests works with the Power BI REST API. Common choices include Python (using the requests library or the azure-identity library for authentication), C# (.NET), JavaScript/TypeScript (Node.js), and PowerShell. Microsoft provides official client libraries for .NET and Python. EPC Group typically implements production integrations in Python or C# depending on the client's technology stack.
Is there a row limit when querying data via the API?
Yes. The Execute Queries endpoint returns a maximum of 100,000 rows per query. If your query would return more rows, implement pagination using DAX functions like TOPN with an offset pattern, or use the XMLA endpoint which supports larger result sets. For bulk data export scenarios, consider using Power BI's export to Azure Data Lake Storage feature instead of the API.
How does row-level security work with the API?
When using delegated (user) authentication, the API automatically enforces RLS rules defined in the dataset based on the authenticated user's identity. With service principal authentication, RLS is not automatically applied because the service principal is not a "user" with an assigned role. You can use the EffectiveIdentity parameter in the API request to impersonate a specific user and enforce their RLS role. This is essential for multi-tenant applications that serve different users with different data access levels.
Can I write data back to a Power BI dataset via the API?
The Power BI REST API supports push datasets and streaming datasets, which allow you to push rows of data into Power BI for real-time dashboards. However, you cannot write back to import-mode datasets or modify existing data in a standard dataset. For read/write scenarios, the XMLA endpoint (Premium only) supports processing operations. If your use case requires true write-back, consider using Power Apps or a custom application that writes to the underlying data source, which Power BI then reads.