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.
“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.
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.
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:
Step 2 – Assign a name to your task:
Step 2 – Set the cadence for your task (E.g. Daily, Weekly, Monthly, or Manual):
Step 3 – Setup the time schedule for the task:
Step 4 – Select file destination after the task completes (Select File, Folder, or Network Share):
Step 5 – Select either a file or file folder to update:
Step 6 – Select the file to “update”:
Step 7 – Select a destination folder:
Step 8 – Check boxes for Append time stamp to output file names & Don’t refresh workbook connections:
LAST STEP – Hit finish and you’re done:
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:
Select Publish Through OneDrive to save in a local folder:
Same for Excel – Check boxes for Append time stamp to output file names & Don’t refresh workbook connections:
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.