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 user must have 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.
  • The tenant setting Dataset Execute Queries REST API, found under Integration settings, must be enabled.
  • 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.
  • Maximum of 120 requests per user per minute. Target dataset does not impact this rate limit.
  • Service Principals aren't supported for datasets with RLS per RLS limitations or with SSO enabled. To use Service Principals, make sure the admin tenant setting Allow service principals to user Power BI APIs under Developer settings is enabled.

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

URI Parameters

Name In Required Type Description
datasetId
path True
  • string

The dataset ID

Request Body

Name Required Type Description
queries True

The list of dataset queries to execute

impersonatedUserName
  • string

The UPN of a user to be impersonated. If the model is not RLS enabled, this will be ignored.

serializerSettings

The serialization settings for the result set

Responses

Name Type Description
200 OK

OK

Examples

Execute queries example

Sample Request

POST https://api.powerbi.com/v1.0/myorg/datasets/cfafbeb1-8037-4d0c-896e-a46fb27ff229/executeQueries
{
  "queries": [
    {
      "query": "EVALUATE VALUES(MyTable)"
    }
  ],
  "serializerSettings": {
    "includeNulls": true
  },
  "impersonatedUserName": "someuser@mycompany.com"
}

Sample Response

{
  "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

DatasetExecuteQueriesError

The details of an error, if present.

Name Type Description
code
  • string

The code associated with the error

message
  • string

The message of the error. If not present here, this information my also be found in details object nested under the error object.

DatasetExecuteQueriesInformationProtectionLabel

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

Name Type Description
id
  • string

The identifier (guid) of the information protection label

name
  • string

The display name of the information protection label

DatasetExecuteQueriesQuery

A dataset query

Name Type Description
query
  • string

The DAX query to be executed

DatasetExecuteQueriesQueryResult

The results from a single dataset query

Name Type Description
error

The details of an error, if present.

tables

A list of tables data for a query

DatasetExecuteQueriesRequest

A request to execute queries against a dataset

Name Type Description
impersonatedUserName
  • string

The UPN of a user to be impersonated. If the model is not RLS enabled, this will be ignored.

queries

The list of dataset queries to execute

serializerSettings

The serialization settings for the result set

DatasetExecuteQueriesResponse

The response to a dataset execute queries request

Name Type Description
error

The details of an error, if present.

informationProtectionLabel

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

results

The list of results, one per input query.

DatasetExecuteQueriesSerializationSettings

The serialization settings for the results of a dataset query

Name Type Description
includeNulls
  • boolean

Whether null (blank) values should be included in the result set. If unspecified, the default value is false.

DatasetExecuteQueriesTableResult

A table of data

Name Type Description
error

The details of an error, if present.

rows
  • object[]

A list of rows