Share
Report
8Hi all,
I have been searching online for a solution to this and unable to find what I really need, though I might not even be searching for the correct terminology. So, I am looking for some general input to describe (with correct terminology) what I need to do and some direction on where to find it.
My company has a SQL server with sales data. I have built an Excel dashboard that pulls proposal data from the SQL table rows via Power Query and slices that data, does calculations and measures, etc. For example, it takes the raw proposal data and determines the number of active proposals from each of my company's offices. I want an automated (set-it-and-forget-it) way to take a daily snapshot of this dashboard data and save it in a new row in a separate Excel table. Again, this is not raw data from the SQL list, but it's the sliced and diced data output from my Excel dashboard file.
This new data table would have a row for each day - the first column would be the date the data was obtained and the other columns would be the "sliced and diced" data from the Excel dashboard. In other words, I want to create a historical data table of the proposal statistics pulled out of the dashboard. The catch is that I need the Power Query to refresh via Power Automate or some other automated method.
What I have tried thus far:
1. Looked at using Power Automate to refresh the Excel Power Query. Power Automate can't do this nor can it run macros.
2. Looked at using Excel online scripts to "Refresh All" but found that online scripts cannot actually refresh a query.
3. I have figure out how to dump data into a new row in an excel table and this works. Just can't figure out how to auto-refresh and get the new daily data into excel.
If I rebuilt the Excel dashboard into Power BI, can Power Automate then be used to do a daily refresh?
If anybody could help point me in the right direction or give me an outline of what the flow would need to look like, it would be a great help. Also if any of my assumptions or conclusions above are not correct please let me know.
All responses (
Answers (
