Can I have Power Automate refresh a Power Pivot table daily, and then save as?

[GooseHelpful199]

I have a PivotTable that is populated via Power Pivot, and each day I need to refresh this sheet and then save the data so that I have a history of each day's data at the time.

Would it be possible to have Power Automate refresh my Power Pivot sheet and then save the file under a new name? Bonus if its possible for Power Automate to also copy/paste that data as text in case people happen to open these files and cause the data to refresh.

[Shravan · Accepted solution]

Yes, it's possible to automate the process of refreshing a Power Pivot sheet and saving the file under a new name using Power Automate. Additionally, you can have Power Automate copy and paste the data as text to prevent automatic refreshes when the file is opened. Here’s a step-by-step guide on how to achieve this:

### Prerequisites
- **Power Automate Desktop**: You'll need Power Automate Desktop to automate Excel-related tasks.
- **Excel Desktop Application**: Ensure you have the Excel desktop application installed.
- **Basic Knowledge of Power Automate**: Familiarity with creating flows in Power Automate.

### Steps to Create the Flow

1. **Open Power Automate Desktop**: Open Power Automate Desktop and create a new flow.

2. **Add Excel Actions**:
- **Launch Excel**: Add the "Launch Excel" action and set it to open the specific Excel file with your Power Pivot data.
- **Refresh All Data Connections**: Add the "Refresh All Data Connections" action to refresh the Power Pivot data.
- **Wait for Refresh**: Add a "Wait" action to give enough time for the data refresh to complete.
- **Save As**: Add the "Save As" action to save the Excel file under a new name, using a timestamp or date in the filename to differentiate each day's file. For example, `Data_yyyyMMdd.xlsx`.

3. **Copy and Paste as Values**:
- **Select Range**: Add the "Select Range" action to select all the data in the necessary sheet.
- **Copy**: Add the "Copy" action to copy the selected data.
- **Paste as Values**: Add the "Paste as Values" action to paste the copied data as plain text values, which prevents the data from refreshing when the file is opened later.

4. **Close Excel**: Add the "Close Excel" action and choose to save the workbook.

### Example Flow Configuration

#### Launch Excel
- Launch Excel and open the workbook:
```plaintext
File Path: C:\path\to\your\file.xlsx
```

#### Refresh Data
- Refresh all data connections:
```plaintext
(This action will refresh the Power Pivot data)
```

#### Wait for Refresh
- Wait for a few seconds to ensure the refresh completes:
```plaintext
Duration: 30 seconds (adjust as needed)
```

#### Save As
- Save the workbook with a new name:
```plaintext
File Path: C:\path\to\history\file_yyyyMMdd.xlsx
```

#### Select Range
- Select the range of data you want to copy:
```plaintext
Worksheet: Sheet1
Range: A1:Z100 (adjust based on your data)
```

#### Copy and Paste as Values
- Copy the selected range and paste as values:
```plaintext
(Use Excel actions to copy and paste as values)
```

#### Close Excel
- Close the workbook and save changes:
```plaintext
Save changes: Yes
```

### Creating the Flow in Power Automate Desktop

1. **Launch Power Automate Desktop**: Open Power Automate Desktop and create a new flow.
2. **Add Actions**: Add and configure each of the actions as described above.
3. **Test the Flow**: Run the flow to ensure it works correctly, making adjustments as needed.

### Scheduling the Flow

- **Power Automate Cloud**: Use the Power Automate web portal to schedule this desktop flow to run at a specific time each day.
- **Desktop Scheduler**: Alternatively, use Task Scheduler in Windows to run Power Automate Desktop flows at scheduled times.

By setting up this flow, you'll be able to automatically refresh your Power Pivot data, save the file under a new name daily, and ensure the data is saved as text to prevent unwanted refreshes when the file is opened.

[Joseph_Fadero]

Hi @GooseHelpful199 ,

I trust you are good, Yes the Powerautomate desktop approach should work for your specific use case scenario, as suggested by Shavran,
I would also like to propose another way you could possibly handle the refresh issue, to also give you more options as well,
If you right click on the Connection properties you would see an option to set an automatic refresh on the data at a specific time interval. 

In regards to saving the worksheet, You could also use a Macro, to save the worksheet at a specific destination in your folder, as well as also use that macro to set a copy and paste as a special value in a particular range.

I am providing the alternate approach to give you more options as well, You can carry out the Powerautomate Desktop process as well @Shravan properly highlighted the step by step actions you may need to take, though one thing you may want to consider is licensing, However, you should be able to achieve the process with the free trial.


Kindly feel free to like my response and mark as a solution if it meets your requirement.

[GooseHelpful199]

Thank you so much, I finally got around to trying this out and seems to work well, thanks for your help!

[GooseHelpful199]

I prefer the power automate option above due to text pasting built in, but I didnt know about this feature and is certainly useful for other Power Query tools that I just need the table updated on, thanks!