Datasets - Execute Queries

Reference

Service:: Power BI REST APIs

API Version:: v1.0

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

Request Body

{
  "queries": [
    {
      "query": "EVALUATE VALUES(MyTable)"
    }
  ],
  "serializerSettings": {
    "includeNulls": true
  },
  "impersonatedUserName": "someuser@mycompany.com"
}

Sample response

Status code:: 200

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

Share via