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 29 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 former Microsoft Gold Partner (2003–2022, the oldest in North America) and current Microsoft Solutions Partner, we have direct access to Microsoft engineering resources when complex issues arise.

Frequently Asked Questions