Re: Is it possible to use PowerBI/Automate to auto...

Anonymous · ‎09-28-2023

I've been following this one over the years and I know there have been updates recently. Every day, I open an excel file, refresh a dataflow, save as csv and to a client.

I saw the thread about locating the file on azure, but I won't have that level of access with the size of my organization. I have premium powerbi access & premium workspaces, but thats about it.

I've also seen other workarounds, but they end up being powerBI reports/PDFs instead of the raw data file we need. I saw one with a script that works in PowerBI desktop, but not in the cloud. I've tried searching for other workarounds, knowing that if I can get it into sharepoint as a csv or a list that I could get into an email. It seems like everything still involves a manual trigger, which kind of defeats the purpose.

A lot of threads are older as well, and I don't have the same button/features in the interface anymore to try. Am I at a dead end?

Anonymous · ‎09-28-2023

I cannot tell you how much I have been experimenting with this very issue over the last week. If someone has a solution, I will be forever thankful. I finally got a query working in Excel and need to export into .csv and email the export to a client, but like you said, the refresh portion is the issue. I tried Power BI options for the refresh but keep running into a wall there, too.

christinepayton · ‎09-29-2023

Yes, you can do this in Power Automate with the "query a dataset" action. Here's a how-to video: https://www.youtube.com/watch?v=5-0KH0IiDjU

Anonymous · ‎10-04-2023

This seems to have almost gotten me there but maxed out at 50kb or 500 rows

christinepayton · ‎10-04-2023

Yes, the query a dataset does have that limitation. You could potentially break up your query into multiple flows to get around this (just apply a filter to the query). It's possible someone else has a more creative solution also.