A few years ago, on February 14th, 2013, I fell head over heels onto a rocket ship that kicks just as much, well it kicks more ass than Tyler Durden on his best day. Valentine’s Day coincidence? Maybe I’ll get to a little more on that in a minute.
Let me fast forward to this morning. I was reminded while reading about Rumblr, that regardless of my thoughts on impact, I promised a blog on something refreshing. When asked, I was buried and honored. I’m still buried, but, it seemed like a good time to distract myself a bit, and shit, how cool is Rumblr.
Now to another timeline rewind. We go back to the beginning.
Since the beginning is always a little bit before the start, we could even rewind to 1999 when I had to learn some stuff in Excel to do my job. I had signed up for a newsletter on this newfangled interwebsuperhighway of information, to help me gain some, well, information. I had bludgeoned Excel and my brain, my way, until I needed help and I reluctantly reached out to Francis Hayes. Dude responded to my email and helped me. The guy even went as far as to call me a few years later when I asked for help again, just to make sure he understood my problem. He freely gave to me. A reader of his tips I became.
So back to the Cheese. An Excel Addict newsletter comes, like clockwork, and there is something about PowerPivot in it. Curious, I click on a link, land on the old here, spend the next hour reading “What is PowerPivot” and a few posts. My mind grapes explode and re-congeal a few times. I read some more posts and had a few more braingasms. Knock-kneed and trembling, feeling something like the Pythagorean version of Funkadelic just did it to me in my ear hole, I went and puked something like “YOU’LL NEVER BELIEVE WHAT I THINK I CAN DO WITH THIS THING CALLED POWERPIVOT” all over our COO’s office for the remainder of the day, well weeks, well, if you ask him, I still say it oh so very often.
So, I unknowingly started hiking down Revolution Road. I didn’t know it then but, this thing called PowerPivot was to be a life changer. I work at a perennial propagation nursery and have the title of Special Ops. We make ~7 million plants a year and I solve the problems. I deem PowerPivot something that needs to get used and I start producing some rudimentary data models. I swirled around on my treadmill of trial and error until I got too bogged down to solve what was probably a simple issue and reached out for help again. This time I offered free plants in exchange because we are high-tech farmers and like to barter whenever possible.
During the first email exchange this guy Rob Collie says:
So this dude that wrote this book…and has this blog thing…totally responded. Then he helped fix my problem and told me my data was cool! He freely gave to me…the plants were just a bonus. Dude helped me just to help me.
WTF sort of data geek Nirvana vortex did I fall into? Hell, looking back, I didn’t even know there was a data gene or that I had one.
Anyway, I send some plants to his dad, who some of you might know, turns out to be super cool too.
I have to remind myself that this is supposed to be refreshing.
So, I continue learning and modeling and giving insight into the lives of my internal customers and peeps here at the Nursery.
We begin to build a state of the art 1.3 acre under one roof production range. The Big Guy asks “How the hell am I going to run Operations, become the General Contractor for this project, and budget for it accordingly when all of the bills are going to get coded to one GL account?”
I quickly reply with what has become my answer to so many problems here at North Creek, “PowerPivot” and I start to hammer out a solution.
I found a field on the Voucher’s we could use for this project to “code to” using my new little Greenhouse Project Chart of Accounts that I whipped up in a table in Excel.
I then linked those two fields and used PowerPivot to “Distribute” that money from the lumped Leasehold Improvement account into all of the “sub” accounts I set up outside of our database in the handy dandy Greenhouse Project.xls data model.
Now the Big Guy could see what project within the project was going over or under budget.
Things were better for him. That’s what keeps me going, making things better for people.
Then he grumbled one morning about how there were so many pieces to each one of the projects. How would he ever keep track? I took a day at home and came up with a time management piece to add to that same workbook.
I made him a table to keep all of his tasks in, start, end, due dates, and named it Completed
Then I added to the model the new tabs, to show what was done, how long he’d been working on it, etc. AND gave him a flattened version of each portion of the project with hyperlinks from a Table of Contents to those pre-filtered flattened tables and hyperlinks back to the Table of Contents.
I gained all of those superpowers for the cost of a few emails and the price of a book (I hadn’t even gone to a PPU yet). The Big Guy found this simple solution so refreshing that he asked me to add it to all of the models.
Now, all of the consumers of my models, open a version, open the PP Window, and refresh the data constantly. There are so many gears whirring in this machine that they all want to see NOW when they are making a call. One of the biggest hang-ups in teaching them how to refresh the PowerPivot data was that they forget to refresh the model after that. This “double” refresh causes some problems. They might pre-filter with slicers, refresh the PowerPivot data, and not reselect a slicer or right-click refresh. Not good. Solution needed…
Enter the disconnected, single line, pasted right into PowerPivot, Refresh table. A single column with a single row. The header is completely optional. Open Excel, type “Refresh,” Copy then Paste.
This is the first slicer I drop on 90% of my data models.
Top Right, always there, reminding them that they need to Refresh.
I also tend to use Pivot Table Connections in the Slicer Tools. I use these to link each Refresh slicer to all of the pivot tables in my workbooks. This slows down performance a bit but, the ease of which the data is consumed and the savings PowerPivot creates in our constantly changing decision process, far outweighs any model “spinning” for a few extra seconds.
Forget bending spoons with your mind – there’s no money in it.
It takes a special kind of mindset to “bend” data (and software!) to the human will. As this article demonstrates, we at PowerPivotPro can twist Power BI into a pretzel if that’s what an organization needs. (A robust, trustworthy, industrial-strength pretzel of course).
The data-oriented challenges facing your business require BOTH a nimble toolset like Power BI AND a nimble mindset to go with it. And as Val Kilmer / Doc Holladay once said, we’re your huckleberry.