Two part question:
(1) Use Power Automate to Automatically have Excel power query tables auto refresh without having to open the excel workbook.
(2) Trigger a Power Automate Flow to select rows from excel and send emails automatically based on each row WITHOUT having to open the Excel workbook. This is my flow (see screen shot). I currently cannot run a test manually or automatically (see screen shot). Ideally I would like to trigger this flow to execute daily AFTER the refresh of the data.
Power Automate to automatically refresh Excel and send emails without manual interaction
Viewed 11445
2 Answers
As of now, Power Automate only supports Power Query with SQL data, so the Power Query refresh within Excel cannot be done. However, this could be accomplished with Power BI instead.
You can copy your queries from Power Query in Excel to Power Query in Power BI, then publish to the Power BI Service. You can schedule the report to be automatically refreshed on a schedule from within the Power BI Service or via Power Automate. Power Automate can then also update an Excel table if needed. If you opt to put your queries in a Power BI dataflow instead, there is a separate flow trigger that you can use for When a Dataflow Refresh Completes.
Here are some resources that may help you.
Publish datasets and reports from Power BI Desktop
When a Dataflow Refresh Completes
Import Excel Workbooks into Power BI
Easily Copy all queries from a PBIX to Excel and vice versa
I don’t yet see a way to refresh a Power Query table without Power Automate Desktop. But you can try using Excel batch actions to efficiently sync another dataset with an Excel table & run automatic updates from the cloud.
Excel batch update, create, & upsert: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/m-p...
Demo video: https://youtu.be/HiEU34Ix5gA
Excel batch delete: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Delete/m-p/1634375#M735
Related