topic Re: PowerBI API execute Query throwing error for certain tables/columns in Developer

PowerBI API execute Query throwing error for certain tables/columns

DomFernandez — Thu, 25 Aug 2022 15:47:50 GMT

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.

Re: PowerBI API execute Query throwing error for certain tables/columns

Daryl-Lynch-Bzy — Thu, 25 Aug 2022 21:20:32 GMT

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.

Re: PowerBI API execute Query throwing error for certain tables/columns

Anonymous — Fri, 26 Aug 2022 05:34:34 GMT

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

Re: PowerBI API execute Query throwing error for certain tables/columns

Daryl-Lynch-Bzy — Fri, 26 Aug 2022 07:57:26 GMT

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.

Re: PowerBI API execute Query throwing error for certain tables/columns

DomFernandez — Fri, 26 Aug 2022 08:12:03 GMT

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.