Update a single Power BI table using Power Automate

[roger100x100]

Hello everybody! I have this Power Automate flow:

What this flow does is that it evaluates the input data coming from my report, performs a login POST request (loginAction, which is a custom connector) to a local Python Flask REST service, and then performs another POST request (evaluateAndSaveAssociationRules, another custom connector) to another endpoint of the same Python service for getting some data in order to visualize them in a table: in particular, this last endpoint evaluates data that need to be shown in the table of my report, and put them in a local MongoDB collection, so that when the flow calls the Refresh a dataset, the report updates, because of the usage of the ODBC data source that directly pull data from that local MongoDB collection.

Everything works fine, but it takes a lot to update the entire dataset: so, what I was trying to do is to only update the table related to the data that I need to show into the table view, instead of the entire dataset. After a lot of days, I found some solutions related to XMLA endpoints, but I came up that I can't use them, because as stated here, I also need to enable read/write for the Premium capacity, and I can't do that because I haven't the Power BI administrator role, and my account has a free Power BI Pro license (the one of 60 days). Also, given that my account is my university account, I don't really know who I could ask in order to have the administrator role unlocked.

So, I found this blog post, and I tried to do the same, but unfortunately I get 403 (forbidden). Below you can check my connector and how I tested it.




These are the response headers I get:
{
"content-length": "0",
"date": "Mon, 31 Oct 2022 00:39:03 GMT",
"requestid": "659db608-3ea2-496f-a691-7ff56de192ab",
"strict-transport-security": "max-age=31536000; includeSubDomains",
"x-content-type-options": "nosniff",
"x-frame-options": "deny",
"x-ms-apihub-cached-response": "true",
"x-ms-apihub-obo": "false"
}

Again, I tried to search for a solution, and I find some posts and discussions, like this one, but I don't really understand how the part related to the creation of the app on Azure and my Power BI report should be linked.

Maybe I'm missing something? Maybe I get 403 because I don't have the Power BI administrator role? Or is there something else?

Or, more in general, do you have some other suggestions on how how I could update a single table in Power BI by using Power Automate? Consider that the Python Flask REST Service must be in my solution, so endpoints that I'm using through customs connectors can't be removed from the flow.

Thanks in advance guys, any suggestions and help are greatly appreciated.

[roger100x100]

After a lot of days I finally came up with a solution. Basically, the only way to update a single Power BI table beyond XMLA endpoints, is to use the Power BI Rest API. The only thing that you need to do before calling the Refresh endpoint, is to get the access token, which you need to pass as a Bearer token to the refresh endpoint, and this was the reason why I was getting 403.
From what I have tried, I wasn't able to pass the Bearer token to the the second custom connector of my Power Automate Flow, which had to execute the refresh of the table, so I don't know if it is actually possible to do that, but I managed to solve in another way, so I report what I did below in case someone could find this helpful.

Given that I was also using a Python Rest Service with Flask, and given that I was calling an endpoint with my second custom connector in my flow above, I directly refreshed the Power BI table at the end of the endpoint by using Python. In order to do this, as I have already said above, you first need to retrieve an access token: I found this guide very helpful, in particular in the REST section, where he put the Python code snippet needed to do so (the username is your Power BI email and same for the password).
For the client_id, you need to register an app on Azure, but the link also showed in the Youtube video that the guide provides didn't work as expected for me, so you need to go at this link to register the app (I chose Embed for your customers).
Then, in addition to what the guide says, I also granted the Read All Datasets permission and, as stated in the authentication section, on Azure in the Authentication section under Management I inserted https://api.powerbi.com as redirect url.

After all this, you should be able to retrieve your bearer access token.

Then you can finally refresh a single table in Power BI, for example by passing a body like the one you find in this blog. In my case I also did this with Python.

I hope I can help someone else 🙂

[onaona]

I am struggling with the exact same issue. Could you be so kind to share the Power Automate flows? Thanks a bunch.

[roger100x100]

@onaona Hi! If you mean my original flow you can ckeck it above in my original post. Or do you have any other doubts? Because if you have my exact same problem (403 when you try to call the Refresh endpoint for a single Power BI Table on Power BI Service) this is normal as long as you have a license which is neither Premium nor Premium per user.

[lbendlin · Accepted solution]

Enhanced refresh with Power BI REST API - Power BI | Microsoft Learn

[roger100x100]

@lbendlin Thank you, I wasn't able to find that documentation page. So, given the requirements, I'm getting 403 when trying to use the refreshes endpoint because I don't have Power BI Premium, right?

[lbendlin]

I am not sure actually. The documentation makes it sound as if PPU is supported.  I cannot test that (I only know Premium) so I cannot help any further. Maybe someone else can chime in.

[roger100x100]

@lbendlin yes Premium per User should be supported for XMLA endpoints, as well as Premium. What I meant is that, looking at the requirements, maybe I'm getting 403 when trying to use the refreshes endpoint because I have neither the Power BI Premium nor the Power BI Premium per User license, because right now I have the 60 days Power BI Pro free trial, as I mentioned in my post above. Thank you for the support.