Datasets - Execute Queries

Executes Data Analysis Expressions (DAX) queries against the provided dataset. The dataset must reside in My workspace or another workspace.

DAX query errors will result in:

• A response error, such as DAX query failure.
• A failure HTTP status code (400).

A query that requests more than one table, or more than the allowed number of table rows, will result in:

• Limited data being returned.
• A response error, such as More than one result table in a query or More than {allowed number} rows in a query result.
• A successful HTTP status code (200).

Columns that are fully qualified in the query will be returned with a fully qualified name, for example, MyTable[MyColumn]. Columns that are renamed or created in the query will be returned within square bracket, for example, [MyNewColumn].

Permissions

The tenant setting Dataset Execute Queries REST API, found under Integration settings, must be enabled.

The user must have dataset read and build permissions. For more information, see Manage dataset access permissions.

Required Scope

Dataset.ReadWrite.All or Dataset.Read.All

Limitations

• Datasets that are hosted in Azure Analysis Services or that have a live connection to an on-premises Azure Analysis Services model aren't supported.
• One query per API call.
• One table request per query.
• Maximum of 100,000 rows or 1,000,000 values per query (whichever is hit first). For example if you query for 5 columns, you can get back max 100,000 rows. If you query for 20 columns, you can get back max 50,000 rows (1 million divided by 20).
• Maximum of 15MB of data per query. Once 15MB is exceeded, the current row will be completed but no additional rows will be written.
• There's a limit of 120 query requests per minute per user, regardless of the dataset that's queried.
• To use Service Principals, make sure the admin tenant setting Allow service principals to use Power BI APIs under Developer settings is enabled. However, regardless of the admin tenant setting, Service Principals aren't supported for datasets with RLS per RLS limitations or datasets with SSO enabled.
• Only DAX queries are supported at this time. MDX and DMV queries are not supported.
  
  

POST https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/executeQueries

URI Parameters

Request Body

Responses

Examples

Execute queries example

POST https://api.powerbi.com/v1.0/myorg/datasets/cfafbeb1-8037-4d0c-896e-a46fb27ff229/executeQueries

{
  "queries": [
    {
      "query": "EVALUATE VALUES(MyTable)"
    }
  ],
  "serializerSettings": {
    "includeNulls": true
  },
  "impersonatedUserName": "[email protected]"
}

{
  "results": [
    {
      "tables": [
        {
          "rows": [
            {
              "MyTable[Year]": 2010,
              "MyTable[Quarter]": "Q1"
            },
            {
              "MyTable[Year]": 2010,
              "MyTable[Quarter]": "Q2"
            },
            {
              "MyTable[Year]": 2011,
              "MyTable[Quarter]": "Q1"
            }
          ]
        }
      ]
    }
  ]
}

Definitions

DatasetExecuteQueriesError

The details of an error, if present.

DatasetExecuteQueriesInformationProtectionLabel

The details of the information protection label, if any, associated with the dataset.

DatasetExecuteQueriesQuery

A dataset query

DatasetExecuteQueriesQueryResult

The results from a single dataset query

DatasetExecuteQueriesRequest

A request to execute queries against a dataset

DatasetExecuteQueriesResponse

The response to a dataset execute queries request

DatasetExecuteQueriesSerializationSettings

The serialization settings for the results of a dataset query

DatasetExecuteQueriesTableResult

A table of data