PowerBI API execute Query throwing error for certain tables/columns

[DomFernandez]

Hi everyone,

when trying to execute 

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

against tables:

-  $SYSTEM.TMSCHEMA_TABLES

-  $SYSTEM.TMSCHEMA_COLUMNS

using query  SELECT * from $SYSTEM.TMSCHEMA_COLUMNS / SELECT * from $SYSTEM.TMSCHEMA_TABLES

we get the following error.

b'\xef\xbb\xbf{"results":[{"error":{"code":"DataExtensionError","message":null,"details":[{"code":"AnalysisServicesErrorCode","detail":{"type":"1","value":0}}]}}]}'

We investigated a little bit, finding out that as long as you only select columns without integers, the call works perfectly fine. However the moment we select columns with integers, error message above appears.

Any idea how we can overcome that issue, as we would at least need the column types which are parsed as integers?

Thanks alot for your support.

[Daryl-Lynch-Bzy]

Hi @DomFernandez - I am surprised that you managed to get any data from the $SYSTEM tables new the REST API.  These normally expect DAX query instead of the DMV query.   The DMV query would work using the XLMA endpoint in SMSS or Tabular Editor.  It will be interesting if this is possible using the Rest API instead.

[Anonymous]

Yes @Daryl-Lynch-Bzy 
the REST API for executequeries on dataset generally made available from May 2022 update.

[Daryl-Lynch-Bzy]

Hi @Anonymous & @DomFernandez - that is correct, and I am aware of that, but the document clearly states the first sentence: "Executes Data Analysis Expressions (DAX) queries against the provided dataset."  {link}

You are executing SQL query or DMV query which works with the XLMA Endpoint, so I am not sure that it will compatible with the REST API.

[DomFernandez]

Hi @Daryl-Lynch-Bzy,

valid point.

Thanks for your answer.

More or less we just thought to give it a try with DMV Queries, which as described works to a certain extent. So I was wondering whether anyone else tried it so far and found some way to solve the problem we faced.