How to export Power BI output to Excel through Power Automate?

[dinosainsburys]

Right now, I have a flow which gets the data from a PBI table and creates a CSV file from it and saves it to a SharePoint location. I want it to go from PBI to Excel or if not CSV to Excel but I have been having difficulty doing it. I have looked at some of the actions but they require an API key which I cannot get as my organisation does not allow me to use them kind of external apps. Any suggestions, please? I would prefer to use Excel as I can do data transformations as needed. 

[lbendlin]

- Run the DAX Query

- stuff the "First Table Rows"  into an Excel table

Done.

If you want this to be fast you would need to use the Graph API.  For a pedestrian solution you can use the default "Add a row to an Excel table"  action.

[dinosainsburys]

So my flow is currently like this

Run a query against a dataset (PBI) -> Add a row into a table (Excel)

I haven't seen anything update on my Excel table? 

[lbendlin]

You need to loop over the first table rows.

You need to have named table in Excel 

Show your flow.

[dinosainsburys]

[lbendlin]

I got some bad news for you. "Refresh a dataset" is asynchronous. It doesn't wait for the completion. Your queries are running against the old dataset.

Show the details of the last step. I don't see where Excel comes into play 

[dinosainsburys]

Oh no! 😞 So how can I make sure so it works against the latest data? Could I add a delay, is that what you mean? 

[lbendlin · Accepted solution]

No, a delay won't help as you never know how long it will take this time.  You need to poll the refresh history and check if the latest entry has an end date (meaning it's done) or not (meaning it is still in progress).

Personally I use a custom action with enhanced refresh and request ID.