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

Name In Required Type Description
datasetId
path True

string

The dataset ID

Request Body

Name Required Type Description
queries True

DatasetExecuteQueriesQuery[]

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

DatasetExecuteQueriesSerializationSettings

The serialization settings for the result set

Responses

Name Type Description
200 OK

DatasetExecuteQueriesResponse

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

Name Description
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

DatasetExecuteQueriesError

The details of an error, if present.

tables

DatasetExecuteQueriesTableResult[]

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

DatasetExecuteQueriesQuery[]

The list of dataset queries to execute

serializerSettings

DatasetExecuteQueriesSerializationSettings

The serialization settings for the result set

DatasetExecuteQueriesResponse

The response to a dataset execute queries request

Name Type Description
error

DatasetExecuteQueriesError

The details of an error, if present.

informationProtectionLabel

DatasetExecuteQueriesInformationProtectionLabel

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

results

DatasetExecuteQueriesQueryResult[]

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

DatasetExecuteQueriesError

The details of an error, if present.

rows

object[]

A list of rows