Recurring flow to automatically update Excel file on One Drive - Excel pulls data using power query from Sharepoint list onto one tab and distributes via power query to other tabs

[Sportynick]

Read several posts on refreshing Excel power query using Power Automate, however haven't been able to find a post that provides a workable solution. I've an Excel file on One Drive that uses power query to pull data from a Sharepoint list onto one excel tab and distributes the data via power query to other excel tabs. Would like to create a recurring timer Power Automate flow that automatically refreshes the power query that pulls the data from the Sharepoint list and then refreshes the power query scripts that distributes the data to other excel tabs. All of these while the Excel file is closed and located on One Drive. The file is a .xlsx file and the data in each tab is used in a separate bigger Power Automate flow. If able to create this smaller automated recurring flow, might attempt to combine into the bigger flow.

[trice602]

Yes I think you can do this with dataflows, the Dataverse version of PowerQuery.  Have you tried it yet?  From there you can schedule a refresh on a regular basis and essentially do the same things you want to do in PQ.  

So what I have done with my flows is have a dataflow on a scheduled refresh and then at the beginning of my flow in question, start with the trigger, 'When a dataflow refresh completes'.  

[trice602]

One more comment:  dataflows is at https://make.powerapps.com under 'More' 

[Sportynick]

Hi trice602,

Thanks for this, very interesting, am exploring this option. Do I have to recreate the excel file in power apps (essentially copy paste the power query scripts) for this solution to work? Also I've a bigger power automate script that currently reads from each tab in the Excel on OneDrive, it should be able to read from each tab in the recreated power apps file?

[trice602]

To make this work, instead of using PQ at all, replace PQ with dataflows and you can read all your tabs there.  Once you get into dataflows you will see it is identical to PQ, just the Dataverse version.  It should work for all your PQ solutions you described.  If this is a viable solution for you, please mark as a solution so others can find it quickly too!  Always glad to help!  Tom

[Sportynick]

Proceeded to try as it looks viable but encountered a "Dataflow publish failed" error saying that I do not have permission to create or modify tables in Dataverse. Trying to request access currently. Do I need to publish in order for it to be usable in Power Automate? And is there usually additional costs involved in getting permission to publish to Dataverse?

[trice602]

Yes, sorry this is a premium license.  The per user premium is $40/month but well worth it!

[takolota]

@trice602 

Unless Dataflows was recently updated, it only sends data to premium datasources like Dataverse. It won’t work for Excel.

@Sportynick 

If you want to auto-refresh things in Excel, you can have the query data refresh upon a user opening the workbook. There are ways to do that with VBA: https://office-watch.com/2022/excel-automatic-refresh-recalculation-tricks/

You can also try using Power Automate Desktop to open the files & refresh from there.

Or you can set up the data tables you need in your file & use this video & template to batch update the tables using standard Power Automate cloud flows
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-... 

[trice602]

@takolota ,

Understood, I probably didn't understand the requirements.  What I was referring to is instead of connecting to data sources in Power Query, I connect directly to them in dataflows, do the ETL there, and then set the dataflow on a scheduled refresh.  I then use that Dataverse table as the refreshed source and publish to an Excel file/table, csv, email, etc, etc, downstream.