Unanswered

Issues with PowerBI Execute Dataset Query

Like (0) Share

Report

Posted on 23 Aug 2022 23:34:13 by Chrispy_Bites

Attached to the PowerBI datasource is a method called ExecuteDatasetQuery that appears to be for submitting DAX queries to a PBI dataset. I've burned an entire day trying to understand how this thing is supposed to work and your documentation is... lacking. At any rate, I can't get a single query (that otherwise functions everywhere else DAX queries can be executed) to return something meaningful within PowerApps.

Any query, even something as simple as

EVALUATE(MyTable)

returns the following:

I can CountRows on the result of running

ClearCollect(_result,PowerBI.ExecuteDatasetQuery(workspaceGUID,datasetGUID,queryString));

and see exactly the number of rows I'd expect to see when running this query anywhere else, but the collection itself has no columns and now values.

Any help?

P.S., I'd love to do this in Power Automate, since that's a much nicer way to set these things up but y'all don't appear super invested in making that useful so...

Categories:

Connectors

Connecting to Data

All responses (9)

Answers (0)

SergiP 118 on 20 Mar 2024 at 15:51:01

Like (0)

Report
Re: Issues with PowerBI Execute Dataset Query

Thank you! It was exactly what I needed to make it work.
Now I'm able to get the info from PBI into PowerApps but I found that it only works for me. When any other user uses the PowerApp there's an error when launching the PowerBI.ExecuteDatasetQuery. It looks like an authorisation problem:
PowerBI.ExecuteDatasetQuery : {"error":{"code":401,"source":"europe-002.azure-apim.net","clientRequestId":"5a1d25a0-8d61-4ba0-9ade-2218fcef106f","message":"The response is not in a JSON format.","innerError":"Cannot read server response."}}
I checked and the other users have no problem to access the Dataset from PBI, so they do have credentials...
I tried with a Flow and it works when I set it up to use my connection (on the Run Only Permissions) instead of the "Provided by Run Only user".

But I'd like to avoid using an external flow and use PowerBI.ExecuteDatasetQuery directly from PowerApps.
Does anyone know how to deal with the permissions with multiple users?
regards
MikeB31107 3 on 27 Nov 2023 at 17:14:30

Like (0)

Report

Re: Issues with PowerBI Execute Dataset Query

This is it..! Took a lot of digging, works for me too - thanks!
goreavin 13 on 14 Nov 2023 at 13:14:35

Like (0)

Report

Re: Issues with PowerBI Execute Dataset Query

Hi @boi5st0,

Thank you!

Is there a way to debug the outputs that are in Result variable? How did you arrive at this approach?

Also, there is a PowerBI.ExecuteDatasetQueryJson function. Do you know if this one is a bit easier to handle?

Best,

Avi

Best, Avi
boi5st0 21 on 11 Oct 2023 at 06:13:37

Like (4)

Report
Re: Issues with PowerBI Execute Dataset Query

Try this out. For me its working.
Set(Result,PowerBI.ExecuteDatasetQuery("XXXXXXX","XXXXXXX","EVALUATE GetAllCustomerCounts").firstTableRows);

ClearCollect(
colCustomer,
ForAll
(
Result,
{
Customer: Text(Value.'GetAllCustomerCounts[customer]'),
Count: Text(Value.'GetAllCustomerCounts[count]')
}
)
);
PowerFoe 4 on 10 Mar 2023 at 11:46:39

Like (1)

Report

Re: Issues with PowerBI Execute Dataset Query

If anyone else is trying this, check out this post on Reddit here.
jefmeyer 166 on 30 Jan 2023 at 18:26:37

Like (0)

Report

Re: Issues with PowerBI Execute Dataset Query

I'm having the same results. Any luck getting this working?
Community Power Pla... on 04 Jan 2023 at 18:57:10

Like (0)

Report

Re: Issues with PowerBI Execute Dataset Query

Same here... anyone?
darkramuh 11 on 09 Nov 2022 at 18:43:23

Like (0)

Report

Re: Issues with PowerBI Execute Dataset Query

Same here, would like to know the solution
tgayle 2 on 08 Nov 2022 at 21:22:56

Like (0)

Report

Re: Issues with PowerBI Execute Dataset Query

I am having the issue which this function. Any solution yet?