Guest Post by Willem van Dijk
Intro by Avi: I have never met Willem van Dijk in person, but in my mind he has a persona of a scrappy boxer/standup comedian. A priceless combination. He would have you rolling on the floor no matter what. He brings home the reality of a business user trying to get some business intelligence. Your day job is something else – Finance, Marketing, Accounts, Sales, Product planning etc. But you do what it takes to get the data and analysis that you need. Here’s to the fighting spirit! Take it away Willem…
I love to hate secrets just about as much as I hate to love secrets…
Why? Because knowing a secret is great for the ego, yet too much ego is not great for the soul.
Which is why I love reading posts on PowerPivotPro as it is all about sharing secrets which, in essence, are no longer such.
Ironically, at times what one believes to be a secret often results to be common-knowledge, or worse, common-sense.
In my inaugural post I promised that I would share a Pot-roast recipe (of the secret kind) so here we go…
Before we dig into the cooking of the Pot-roast, let’s take a look at what we will need in order to successfully end the day with a filled belly.
- Salt and freshly ground black pepper (Rep)
- 3 – 5 Pound Chuck Roast (Customer)
- 2 Tablespoons of Olive oil (SKU)
- Sprigs of Rosemary and Thyme (Period)
- Assorted legumes (Quantity)
- Cup of Red Wine (optional, and only meant to make you feel better)
Every now-and-again comes that time when the finance departments all over the world start asking the sales departments all over the world whom is going to be doing what… Is it just me or is E.L.O. playing in your heads?
E.L.O. will make you tap your feet
Now, while you are (potentially) enjoying the musical interlude, let’s think about the way sales staff AOTW feel about having to spend time trying to figure out which Customer is going to be selling what SKU in which Period… … … They’d most likely rather have Freddy K visit them in their sleep.
Yet, they are paid to perform such duties once in a while, thus, begrudgingly, they will take on the task at hand. Once finished, if all goes well, they will hand in a standard template with the needed information, which should look something like this:
And we thank them for their hard work done, only to hate them for the task left for us to perform, which is to flatten out the data so that it can work in a Power Pivot model.
Here’s where, with a show of hands, those who know how to do this may quietly leave the room as you probably already know how to make the Pot-roast. Yes, you VBA experts, or those of you who’ve played enough with Power Query to figure this one.
Back in the Kitchen
Don’t know about y’all, but I’ve never been a real good cook. Sure, I can dice and slice, even make a mean ol’ omelet if moved to it, but that Pot-roast, that takes some cooking skills. Now, you can head over to your favorite bookstore (or iTunes or whatever) and pick up a manual so that you can find out all the intriguing and fascinating aspects of the pots and the flames and, most importantly, the best way to wear a chef’s hat!
There are those who can wear the Classical Hat, though it seems that you have to hail from Italy or have worked in Redmond… On the other hand we have the artist who take things to levels un-imaginable by us mere-mortals… and then there’s me; you’ve guessed it, I wear the hat on the right!
In the past, when presented with lists of data as shown earlier, I got down with the manual task of cutting and transpose-pasting the information, eventually making a mistake and having to go back to square 1. Getting bored with the tedious, repetitive and, ultimately, mundane task of ctrl-c, ctrl-arrow down, ctrl-v, rinse, spit and repeat…
What we need here is microwave technology… enter VBA stage right.
Interested in Learning How to Do this Kind of Thing?
Our New Online Power Query Course just launched this year. You’ll learn Power Query from the best in the business, two Excel MVPs Ken Puls and Miguel Escobar. We’ve included 7 Hours of Self-Paced Video over 31 Courses, with lots of example files, and a Digital Badge powered by Acclaim.
CLICK HERE to claim your spot.
VBA Macros lead with a jab
When you think about it, VBA (Visual Basic for Applications) is kind of a weird terminology for something which, in essence, isn’t anywhere near to being Visual, nor Basic. It is a misnomer and should be referred to as WMPAROOTH <—figure that one out, here’s a hint; it involves a hat and a rabbit.
I’ve been trying to wrap my head around VBA for a while now, and I’ve made little progress aside from being able to read it a little bit better. However, writing it to the point where the WOW factor can start lighting my dim lit room is a pipedream akin to me receiving a Michelin star for my omelet. Yet, I knew that there had to be a way, and I was sure that VBA would be able to help me out.
Now I just needed to check out the 182.000 results to find the answer I was looking for… and I will spare you all the wrong answers I found to my question. A click here, a click there, Dim this, Object that—if I would have just spend the time it took reading through all the posts I found and instead performing the old school ctrl-c et al, I would have had enough time to also wash my hair, applied conditional formatting to make the file look prettier and have made me an omelet.
In the end, without the magic code in hand, I decided to head on over to the Microsoft community and propose my query there to see if I could get lucky, after all, if Redmond can’t help me, then we might as well all chuck our hats into the fire.
And BINGO, I got lucky and someone on a message board actually was able to lend the helping hand—thanks to tasosK for the following code which I had to tweak a little to make it work
Works like a charm! Pot-roast came out of the oven smelling all succulent, and the glass of wine was the icing on the cake… that was, until somebody came with a little link to a YouTube video which gave me an instant hangover as I realized that the wine I was drinking was corked
The video, which can be found HERE, was being watched by yours truly just around the time that Rob posted his blog where he was making a point of (or, at least, that is what I got out of it) us sometimes forgetting about the basics and wanting to be artists.
Power Query counters with a one-two punch
Power Query out performs VBA in speed, and, so far, is proving to be a bit more stable too, when it comes to un-pivoting the data set. Not only that, but you don’t have to learn any WMPAROOTH code either, which is a bonus!
Here’s how it works:
Open Power Query and select “From Table”
Once Power Query is open, highlight ONLY the Period columns, right-mouse-click on the column and select the “un-pivot columns” option
And presto, we have what we want, a flattened table which we can use in a PowerPivot model to abuse with =CALCULATE()!!!!!
For myself, this little adventure for the perfect Pot-roast, has taught me a couple of things:
- I need to learn more about Power Query
- I still need to learn more about WMPAROOTH
- When in doubt: ASK!!! The internet, with sites like PPP, YouTube, Wiki, Google et al, makes the Great Library of Alexandria seem to hold fewer pearls of wisdom as one might find at your local Elementary School without internet.
Next time I will explain why wearing a Toga to a Toga party shouldn’t be mandatory…