Apply To Each http connction for PowerBI dataset

[GavC]

Hi all,

I'm finding this very odd so I am hoping someone may have had the same problem. I have two flows.

and 

What they both do is update a PowerBI dataset using this method

The top flow runs on just one dataflow the bottom on all of them. So the datasetId and GroupId are stored in a table. The single  version takes a parameter and this gets the dataset and group stored in a table. The multi version selects all of the datasets and groups then uses an Apply To Each to run the REST API call.

Both versions are run from PowerApps.

So what is happening is the single version works fine, does the update on the PowerBI dataset, and updates my table to say that it has completed the task. However the version using Apply To Each fails.

JSON call and return for the single call

{
"uri": "https://api.powerbi.com/v1.0/myorg/groups/******/datasets/*****/refreshes",
"method": "POST",
"headers": {
"Authorization": "*sanitized*"
}
}

{
"statusCode": 202,
"headers": {
"Pragma": "no-cache",
"Strict-Transport-Security": "max-age=31536000; includeSubDomains",
"X-Frame-Options": "deny",
"X-Content-Type-Options": "nosniff",
"RequestId": "*****",
"Access-Control-Expose-Headers": "RequestId",
"Cache-Control": "no-store, must-revalidate, no-cache",
"Date": "Wed, 15 Dec 2021 15:46:45 GMT",
"Content-Type": "application/octet-stream",
"Content-Length": "0"
}
}

JSON call and return for the multi call

{
"uri": "https://api.powerbi.com/v1.0/myorg/groups/*****/datasets/*****/refreshes",
"method": "POST",
"headers": {
"Authorization": "*sanitized*"
}
}

{
"statusCode": 401,
"headers": {
"Pragma": "no-cache",
"Transfer-Encoding": "chunked",
"Strict-Transport-Security": "max-age=31536000; includeSubDomains",
"X-Frame-Options": "deny",
"X-Content-Type-Options": "nosniff",
"RequestId": "*****",
"Access-Control-Expose-Headers": "RequestId",
"Cache-Control": "no-store, must-revalidate, no-cache",
"Date": "Wed, 15 Dec 2021 16:18:46 GMT",
"Content-Type": "application/octet-stream",
"Content-Length": "0"
}
}

So both calls are exactly the same, one works, one does not. I also have another flow that calls a GET rather than a POST, also on a loop and that fails also. Has anybody else had this issue, or know what the problem is and have a solution?

Many thanks

[ManishJain]

Hi @GavC ,

I am not sure if it helps here but I see you are using HTTP request to refresh dataset in Power BI whereas there are OOB actions available for Power BI. This might help you remove unwanted steps at the top to get bearer token as well. If you can try that first and see if it helps.

Thanks

[GavC]

Thanks Manish,

I tried that method and found the REST API method more versitile, as this structure will/should work for any of the many REST API for PowerBI. Unfortunately it doesn't seem to work in the loop. Yet the calls are correct as they work with a single call. Is there any other settings required if you are hitting the PowerBI server multiple time with API calls. 

[ManishJain]

Hi @GavC ,

Per microsoft blog limits do apply  (check next steps) 

https://powerbi.microsoft.com/en-us/blog/refresh-your-power-bi-dataset-using-microsoft-flow/

• Existing limits on refreshes apply when running the refresh dataset action in Microsoft Flow.  For datasets in shared capacity used by Power BI Pro, your refresh action is limited to eight refreshes per day (including refreshes executed via Scheduled Refresh). In Premium capacities, there is not limitation on the number of refreshes per day, although you are limited by the available resources in your capacity.  If there are not sufficient resources, the refresh execution may be throttled until the load is reduced. If this throttling exceeds 1 hour, the refresh will fail.

Thanks

[GavC]

Hi @ManishJain 

Yes this is correct, you can do 8 refreshes for each dataset per day. I'm am trying to create an app that will update all the datasets with a click of a button. We only refresh each datasets once a day after the pipeline has finished, but for whatever reason, if the pipeline fails then the scheduler will not update these datasets and this is then a manual job. It can take a few hours if you have a lot of datasets to complete this task. Or maybe one will fail so again this will need to be rerun. So the solution is to create a nice app that gives you this detail and you can update either a single or datasets if required. The single update works fine, but it is when I try to update all using the loop that I get the 402 error. I can figure out another way by just using the single ang loop and calling this each time, but I was wondering why there is an error when using the loop to run off all the dataset refreshes, and if anyone has tried the same thing and encountered this issue, and then found a solution. Thank you for your help, I do appreciate it. 

[GavC]

Hi @ManishJain 

I've just revisiting this again, unfortunately the PowerBI modules for Power Automate, are really not fit for purpose. These only allow you to update a dataset so I can run a POST, but not a GET. I'm not sure whether this can also be run in a loop, to update the datasets. Even if this works, i still need the history sadly.

Happy New Year

Gav

[GavC · Accepted solution]

Hi Everybody,

I have found the problem, this solution does work, but frustratingly you need to put a space after Bearer in the http connection Authorization. So Bearer should be followed by the access_token. But make sure there is a space between Bearer and 'access_token'

Thanks everyone.