PowerBI API not running query from executeQueries endpoint

[tech49]

Hi, 

I'm connecting fine to the PowerBI API however I can't run DAX queries.   My end game is to assit the process of ascertaining what datasources and tables are used by what PowerBI reports.

I'm using the following endpoint as shown from Datasets - Execute Queries - REST API (Power BI Power BI REST APIs) | Microsoft Learn:

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

I'm sending the following JSON to the API: 

{
    "queries": [
        "select * from $SYSTEM.TMSCHEMA_PARTITIONS"
    ],
    "serializerSettings": {
        "includeNulls": false
    },
    "impersonatedUserName": null
}

I'm getting the following error...

"message":"Error converting value \"select * from $SYSTEM.TMSCHEMA_PARTITIONS\" to type 'Microsoft.InfoNav.Explore.ServiceContracts.Internal.ExecuteDatasetQuery

[Anonymous]

Given your end game is to ascertain datasource and tables per report, assuming you want something to run across the whole tenant. If this is the case, you would be better served using the Scanner APIs.

All of the following can be obtained from GetScanResult:

Datasets will give you Tables and Datasources (Tables is simply 'tables, Datasources, you will get from datasourceUsages[datasourceInstanceId].

There are many ways you can get to this data, depending on your preference. Personally, I've extended this solution Extracting Power BI metadata with Data Factory (part 1) – justB smart but if you don't need to store the data, the Power BI REST API Connector is much faster to get running Power BI REST API Connector — The Power User.

With your datasourceInstanceId you can related datasets to datasourceInstances (this will then give you more details about that particular datasource.

You can then use reports[datasetid] to link all the above back to your report.

Admin - WorkspaceInfo GetScanResult - REST API (Power BI Power BI REST APIs) | Microsoft Learn

[tech49]

Ok, 

I found the first part of my problem, I serialised the JSON incorrectly. 

I should have labelled the as a "query", as below

{
    "queries": [
       "query": "select * from $SYSTEM.TMSCHEMA_PARTITIONS"
    ],
    "serializerSettings": {
        "includeNulls": false
    },
    "impersonatedUserName": null
}

using that format, I can send a query, however I can't get a TMSCHEMA_PARTITIONS, Now I'm getting 

DataExtensionError, AnalysisServicesErrorCode

[Anonymous · Accepted solution]

HI @tech49,

As the document mentions, current this API only support to send Dax query and T-SQL query should not be suitable for this API usages:

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

Datasets - Execute Queries - REST API (Power BI Power BI REST APIs) | Microsoft Learn

Data Analysis Expressions (DAX) Reference - DAX | Microsoft Learn

Regards,

Xiaoxin Sheng