Many of you may know of our wonderful business partners over at PowerON. Among the services and software they offer is a powerful tool called Power Update that essentially automates the refresh (AND publishing!) of Excel AND Power BI Desktop files.

time machine from cyborg assort

“It’s bigger on the inside.”

I’d love to spend this entire post just trumpeting the features of this product…however Rob has already done that! So I shall direct you to his original posts if you’d like to learn more about the software itself. For this post, I’d like to focus on a specific (and kickass!) way I’ve learned to utilize the tool.

Power Update LogoIntroducing Power Update!

Power Update Adds Email Notifications, Macro Support, and 100% Free Version

Fully-Customizable DAX-Based Alerts

The (Historical Data) Struggle Is Real

Many BI consultants struggle to determine the best practice for capturing historical snapshots of reports and dashboards (there are many right answers). There’s plenty of software out there for it, or you can take the hard road and attempt to do it manually. By manual I mean renaming the file periodically and saving it somewhere else at whatever cadence you determine is best, not ideal in my book. Basically unless you’re lucky enough to have access to a something like SharePoint with near-limitless version history enabled (which is rare), keeping an audit trail is a pain in the booty.

With that being said…I’m here today to show you a straight forward and simple solution to this…using Power Update as your own personal historian. Smile

A Snapshot Is Worth A Thousand Words

The entire premise of Power Update is to create tasks to perform various functions involving Excel or Power BI Desktop files. Now these tasks typically revolving around something involving a refresh (just call me Captain Obvious). However using a bit of ingenuity we can come up with some creative ways to utilize these tasks. There are TWO major customizations in the task settings that let us easily create snapshot tasks.

First off there’s a option to disable workbook refresh. So essentially it’ll run whatever is specified in the tasks, but skips refreshing the file. Secondly, there’s an option when saving the file to append a timestamp to the file name. This allows us to create a uniquely named file every time this task ran. With these two examples in mind why don’t I hop us into a visual walkthrough and show you how to create this task.

For this example I’ll go ahead and create a task to produce a Snapshot of an Excel file every Sunday at 12:00 AM, and save it to a designated Snapshots folder. To make it easier I’ll visually indicate any steps that always need to be set this way when creating a snapshot task.

Step 1 – Create a new task in Power Update:

Power Update Create New Snapshot Task

 

Step 2 – Assign a name to your task:

Power Update Name Version Control Task

 

Step 2 – Set the cadence for your task (E.g. Daily, Weekly, Monthly, or Manual):

Power Update Select Version Control Task Cadence

 

Step 3 – Setup the time schedule for the task:

Power Update Select Versoin Control Task Time Schedule

 

Step 4 – Select file destination after the task completes (Select File, Folder, or Network Share):

Power Update Select Version Control Task Destination

 

Step 5 – Select either a file or file folder to update:

Power Update Select Version Control Task Source Workbooks

Step 6 – Select the file to “update”:

Power Update Select Version Control Task Excel File

 

Step 7 – Select a destination folder:

Power Update Select Version Control Task Destination Location

 

Step 8 – Check boxes for Append time stamp to output file names & Don’t refresh workbook connections:

Power Update Append Time Stamp And Don't refresh Workbooks

 

LAST STEP – Hit finish and you’re done:

Power Update Task Email Settings

 

NEW FEATURE: Coming Soon To An Application Near You!

So the software engineers over at PowerON JUST added a feature update to allow us to create a Snapshot task for Power BI Desktop files as well! This is very exciting news (for me) since I’m continuously leveraging more and more reporting needs in the PBI universe. It’s currently only available in the beta release, but will be available in the next major update. The process is nearly identical to creating the task in Excel save for a couple differences. The main requirement being that we will need to setup the task to Publish to OneDrive, which essentially saves it to a local folder for you.

Select Power BI as the destination type:

Power Update Power BI Desktop Destination Type

Select Publish Through OneDrive to save in a local folder:

Power Update Publish through OneDrive PBI Desktop

Same for Excel – Check boxes for Append time stamp to output file names & Don’t refresh workbook connections:

Power Update Append Time Stamp And Don't refresh Workbooks

 

There you have it, simple right? Now this tool will grab your file and create a snapshot of it at whatever interval you set, it’s your very own personal Way Back Machine! Well this is just the tip of the Power Update iceberg folks, I’ll be posting more “cool” (Iceberg, get it…anyone?) ways to use Power Update as the year progresses. I wanted to keep today’s post sweet and simple (and hopefully useful!), so until next time P3 Nation. Smile

  Subscribe to PowerPivotPro!
X

Subscribe

Reid Havens

Reid Havens is a Principal Consultant for PowerPivotPro and the Owner of Havens Consulting Inc. His main goal is to collaborate with individuals and organizations by helping them analyze data to understand their business. Using his formal backgrounds in technology and organizational management Reid has worked with various local and multi-national companies. He is also an adjunct professor at Bellevue College, guest lectures at the University of Washington, and provides corporate trainings centered around teaching Business Intelligence, Reporting, & Design. 

This Post Has 9 Comments

    1. Why indeed! I have no doubt that PowerUpdate is one of the biggest reasons for all the big success our Business Data Analytics dept has experienced since we were created 18 months ago. I have hundreds of PowerUpdate tasks running sending emailing Excel and PDF files all over the globe, saving files to SharePoint, etc. All running on an old beat-up desktop that my I.T. department gave me out of sympathy. (Note that I received no $$ for this shameless endorsement! I just love PowerUpdate that much.)

  1. Awesome Reid,

    Great Post. One of the things that came out of the Microsoft announcement last week, was that Power BI Free are losing the ability to ‘Analyze in Excel’.

    Power Update can help in some cases here as well:

    If you want Power BI and Excel with Power BI Free:

    1. Build your Power Pivot Model in Excel.
    2. Let Power Update load it to OneDrive on a scheduled basis.(Also works with the Free Version).
    3. Connect the OneDrive file to Power BI, and create Dashboards to your hearts content in the Power BI Service.
    4. BONUS: When you want to Analyze in Excel, just open the file from OneDrive and multiple people can collaborate and author on the same file.

    It’s free.

  2. Thanks, Reid! Very useful article, and I’m sure a lot more people are going to be looking into PowerUpdate.

  3. hello, can you explain why you set the checkbox ‘do not refresh workbook connections’ ? …Shouldn’t you refresh the data when taking a snapshot ?

    1. Kurt,

      When taking the snapshot, you don’t have to load the data. All you are trying to do here is to get a snapshot of your workbook. You can always go back to any of these snapshots, and refresh it from your datasource, and now it will contain all your data.

      Chris.

      1. Hi Kurt/Chris,

        I have this turned off since I actually have a seperate task for refreshing the data itself every other hour. This tasks runs seperately once a day to just snapshot the file, doesn’t need the data refreshed when it does. Hope that clears up some things!

Leave a Comment or Question