skip to Main Content

Objectively Awesome

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.

I am part of a Legion that was moved by some cheese.

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:

Rob email

So this dude that wrote this book…and has this blog thing…totally respondedThen 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.

plants

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.

Greenhouse Project Chart Of Accounts

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.

edit relationship

Now the Big Guy could see what project within the project was going over or under budget.

Budget Balance

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

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.

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.

refresh

This is the first slicer I drop on 90% of my data models.

first slicer

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.

Simple…

Succinct…

Refreshing

sig

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.

Connect with the Experts

Gordon Rowe

Gordon has over 20 years of experience in the horticultural industry and currently holds the position of Special Operations at North Creek Nurseries, a wholesale propagation nursery specializing in native plants. He has held the roles of Grower, Inventory Manager, Shipping Manager, and Production Manager for businesses such as North Creek and The Conard-Pyle Company. He has played a key roll in the production of upwards of 100 million plants. As Special Ops, he spends his time building data models with Power Pivot, honing process with the team, and coordinating projects from Blue Bird trails to time-lapse greenhouse construction films. He spends his own time listening to Bob Dylan’s entire discography in a chronological loop, small stream fishing with an ultralight rig for naturalized trout, modeling for classically trained artists, and gardening at his home with his family.

This Post Has 11 Comments
  1. Hi there, interesting article, I haven’t quite grasped how the ‘refresh’ slicer works (I am a bit hungover though!); could you elaborate a bit more on this? Is it a visual prompt (if so why have you chosen a slicer and not some brightly coloured text?) or does clicking the slicer somehow trigger a refresh?

    Thanks 🙂

    1. Thirsty Thursday live on! I’ll type quietly for you. After you refresh the data in the PowerPivot window, the pivot table still needs to be refreshed by either right click refresh or slicer selection. My internal customers were always forgetting so, the Refresh slicer is both a visual and functional prompt…user refreshes the PP window, minimizes, and then clicks the refresh slicer to update the pivot table. You could take it a step further and use the Slicer Tools tab to make it brightly colored…Thanks for the idea. There are still a few slow learners and that will help.

      1. As far as I know (starting with Excel 2013) after every change in Power Pivot, all PivotTables refresh automatically.

  2. Love it! Regardless of how simple “Farming” might be to those “city folk”, its still a business. Businesses run on information.
    So cool to see someone’s excitement and demonstration of this “cool-thing”
    Gonna have to use that “Data geek Nirvana!”

    1. I am glad you enjoyed my musings. We essentially are selling the idea that we will successfully propagate a plant, be it from seed, cutting, tissue culture or division, a year plus in advance and then execute that plan and deliver that plant on time to our customers all the while juggling space, weather, and a hundred other variables across 500 or so line items and 4 sizes is well, far from simple. PowerPivot has made managing all of that so much easier. It’s really allowed us to focus on eliminating waste while increasing our profits significantly by giving us the ability to define the metrics we needed rather than using the canned reports that worked but, didn’t really make the machine sing.

  3. Do we really need the swearing?? This blog has always been humorous, but I don’t remember Rob using foul language.

    1. Tim I appreciate the question for a couple of reasons but, I must start with saying that restraint of tongue and pen has never been my forte.

      When I read your email notification this morning sipping my fist bit of coffee, I bounced over to search the blog and see if I had set some sort of expletive precedent and quickly came to the realization that the internal search box on the site no longer exists. When I finally got to a computer, I verified that it wasn’t a mobile related thing and went back to google and ran through my kids’ “big people” word list against the site and found that I am not the first. Most of the colorful language I found in the results was relegated to comments, and thankfully not just my own but, even in the main body of posts such as when Matt left Coke for PowerPivot.

      The swearing was also literarily tied to a now defunct link for Rumblr, a Tinderesque app for fighting, which turns out was a hoax however many years after I wrote this, hence the link not working. https://www.snopes.com/fact-check/rumblr/ So thematically, there was intent in both my juvenility and reaching into the lexicon of the teenage pugilist.

      I’m glad you were still able to find some humor.

Leave a Comment or Question