Using Power Update as a Time Machine for Excel (AND NOW Power BI Desktop) files!

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 thumb Using Power Update as a Time Machine for Excel (AND NOW Power BI Desktop) files!

“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 thumb 6 Using Power Update as a Time Machine for Excel (AND NOW Power BI Desktop) files!Introducing 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. wlEmoticon smile 3 Using Power Update as a Time Machine for Excel (AND NOW Power BI Desktop) files!

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:

New Task thumb Using Power Update as a Time Machine for Excel (AND NOW Power BI Desktop) files!

Step 2 – Assign a name to your task:

PU Task Step 1 thumb Using Power Update as a Time Machine for Excel (AND NOW Power BI Desktop) files!

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

PU Task Step 2 thumb Using Power Update as a Time Machine for Excel (AND NOW Power BI Desktop) files!

Step 3 – Setup the time schedule for the task:

PU Task Step 3 thumb Using Power Update as a Time Machine for Excel (AND NOW Power BI Desktop) files!

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

PU Task Step 4 thumb Using Power Update as a Time Machine for Excel (AND NOW Power BI Desktop) files!

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

PU Task Step 5 thumb Using Power Update as a Time Machine for Excel (AND NOW Power BI Desktop) files!

Step 6 – Select the file to “update”:

PU Task Step 6 thumb Using Power Update as a Time Machine for Excel (AND NOW Power BI Desktop) files!

Step 7 – Select a destination folder:

PU Task Step 7 thumb Using Power Update as a Time Machine for Excel (AND NOW Power BI Desktop) files!

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

PU Task Step 8 thumb Using Power Update as a Time Machine for Excel (AND NOW Power BI Desktop) files!

LAST STEP – Hit finish and you’re done:

PU Task Step 9 thumb Using Power Update as a Time Machine for Excel (AND NOW Power BI Desktop) files!

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:

PU Task PBI Step 1 thumb Using Power Update as a Time Machine for Excel (AND NOW Power BI Desktop) files!

Select Publish Through OneDrive to save in a local folder:

PU Task PBI Step 2 thumb Using Power Update as a Time Machine for Excel (AND NOW Power BI Desktop) files!

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

PU Task Step 8 thumb 1 Using Power Update as a Time Machine for Excel (AND NOW Power BI Desktop) files!

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. wlEmoticon smile 3 Using Power Update as a Time Machine for Excel (AND NOW Power BI Desktop) files!

Let’s block ads! (Why?)

PowerPivotPro