CODEWITHSUNDEEP
excelrefreshpowerquerypower-automate
Marley
Marley

Reputation: 1

How do I refresh an Excel Power Query using Power Automate Online?

I have data being pulled from a SharePoint list to an Excel file and I'm trying to use Power Automate online to create a scheduled flow that will trigger the "Refresh All" button for Power Query in Excel (see image). Due to different protections, I'm unable to simply schedule the refresh directly in Power Query in Excel and Power Automate cannot pull the SharePoint list in the format I need. There doesn't seem to be an option in Power Automate online that would connect to Power Query in Excel. Any suggestions?

Power Query Button

Upvotes: 0

Views: 23679

Answers (2)

Alishia Randolph
Alishia Randolph

Reputation: 21

Power Automate doesn't have a feature to allow you to refresh connections or open/close a document. I was also encountering the issue of the document not refreshing on "open" because it was locked by another user (due to using One Drive). I couldn't use Power BI, scripts, or MACROS either.

I needed my workflow to add rows to a spreadsheet, update the pivot tables, and then send an email with the results. Here is what I did to get around the pivot tables not updating.

In Excel:

  1. Open the pivot table options and select "Refresh data on file open". Refresh Data
  2. On the worksheet with your pivot table(s), create a table in a cell that has similar information as below (in the workflow we will update this table so it forces the system to re-open the document and refresh). Once the table is created, select the column and "Hide columns". Updated Column
  3. If you are using One Drive or a shared document like I did, when you have made all the changes you need, you will want to change the session to "viewing", and then close the document. 

*Any time you make changes manually in the document, make sure to "close" the session by changing it to "viewing" or else the workflow will not complete properly due to the doc being locked. Viewing In Power Automate: I have my trigger set to a schedule at the end of the month but your trigger or schedule and parameters can be whatever you set them as. I'm going to show you the steps that are important to the spreadsheet.

*Workflow snapshot: Workflow Snapshot

  1. Add a row into a table > Select the file location and the table > Update all the fields from the SharePoint list (Value= outputs('Get_items')?['body/value'].
  • I used this step to update the spreadsheet automatically with items added to a SharePoint list (so it can be extracted into a report monthly). Add A Row

  1. Delay 1: 10 minutes. This allows One Drive time to close and end the current session.

  2. Update a row: Select the location of the file (this is the table we created and hid earlier). Update Row

  3. Delay 2: 2 Minutes

  4. Get file content (One Drive) > Select the file. I was limited to only being able to see my individual One Drive so make sure your excel workbook is saved in a location you can select in Power Automate.

  5. Convert file (One Drive)> Select the file.  Convert File

  6. Send an email > Attachment Name: (File Name= outputs('Convert_file') > Attachment Content: (Body= outputs('Convert_file'). It may be listed as "File content" in the Converted File section. Send Email

Upvotes: 2

Tyler Kolota
Tyler Kolota

Reputation: 51

There may be a simpler option if there is a way to refresh power query from Power Automate, or if you could use Power BI which is connected to Power Automate.

But there is also dataflows in Power Automate that can work similar to power query. https://youtu.be/8IvHxRnwJ7Q

Then you can do any additional transformations in Power Automate & use Excel batch actions to import all the data to Excel from a flow. https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-p/1624706

Upvotes: 0

Related Questions