skip to Main Content

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…

TopSecretppp

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.

Ingredients

  1. Salt and freshly ground black pepper (Rep)
  2. 3 – 5 Pound Chuck Roast (Customer)
  3. 2 Tablespoons of Olive oil (SKU)
  4. Sprigs of Rosemary and Thyme (Period)
  5. Assorted legumes (Quantity)
  6. 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:

image

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.

image

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!

image

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?

power query

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.

image

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

image

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 Sick smile

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”

image

Once Power Query is open, highlight ONLY the Period columns, right-mouse-click on the column and select the “un-pivot columns” option

image

And presto, we have what we want, a flattened table which we can use in a PowerPivot model to abuse with =CALCULATE()!!!!!

image

For myself, this little adventure for the perfect Pot-roast, has taught me a couple of things:

  1. I need to learn more about Power Query
  2. I still need to learn more about WMPAROOTH
  3. 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…

This Post Has 19 Comments
  1. Power Query is the missing part between any source of data and end report in Excel (or power pivot). You have chosen a very good example – unpivoting data in power query is so simple. It makes work so easy when working with any source of data. I’ve made a power query not long ago to grab data from sharepoint (excel and html files at the same time) under 10 minutes -VBA in that case would be much more complicated. I’m following blogs of Chris Web and Ken Puls – i think they are the best right now in Power Query

  2. My first thoughts on using Power Query were:
    “Wow, this can save me so much effort over writing extensive VBA to do similar data transformation style stuff”
    “This is like the macro recorder in a way, but you can get sophisticated by mastering M code”
    “Using Power Query in it’s standard format and ‘recording steps’ is far more maintainable and transparent than having someone amend VBA and particularly in instances where that person is not experienced in VBA”
    I’ve been in VBA development for a while now and writing robust, transparent, reusable code is an art form. It requires a great deal of effort and attention to detail which of course requires time – time that is rarely appreciated
    If similar automations can be achieved through technology that is embedded/native to Excel then I’m all over them
    Very encouraging to hear that PowerQuery will be embedded into ribbon within Excel 2015. Functionalities like PQ and PowerPivot will become mainstream if users don’t have to go looking for them (installing them)

  3. Hello Willem,

    This is exactly what I needed to hear in more than 1 way: I know I need to get a better handle on Power Query and, after following the link, I am toe-tapping to ELO and a new goal…

  4. Very good and thank you. Sometimes I just need to be reminded about how to do something. In my case it was a large annual budget file with the month names as column headers. After I relearned this step I changed the month names to dates in order to use the calendar table in PQ. Not all that long ago I was doing it like you did, copy and paste, copy and paste, oh nuts messed that up start over. Expensive pot roast back in the day. My next project to relearn is how to extract the metadata columns and delete duplicates for my lookup tables.

  5. Un-pivot is my favourite function in Power Query! I had exactly the same problem with Department budget templates – Rows with multiple analysis fields and monthly columns. Originally found the VBA code but adding more analysis fields has a significant performance impact. Power Query just eats up this sort of data transformations.

  6. Willem, quite an entertaining article. It is great features like this that make Power Query (and the entire “Power suite” for Excel) such a great tool for business. Historically, IT has held the “keys to the kingdom” when it comes to having robust tools to address challenging data integration issues. Now, business analysts and casual data manipulators can perform challenging tasks with the greatest of ease. And you’re right, for the much more complex stuff, there are wonderful advocates and community-minded people who are so willing to contribute to the body of learning via the internet. Thanks for being a part of that!

  7. Willem and Rob, this is an interesting approach. I can’t imagine ever using VBA to unpivot data! While the PowerQuery trick is really slick, there’s an even easier way to do it. Enter the ‘Tableau Reshaper Add-In’. I know, I know, Tableau and PowerPivot have some sort of mock rivalry going, but this add-in is just too sweet not to share. Here are the facts:

    1) It’s totally free and 100% not related to the Tableau software at all
    2) It’s a lightweight Excel add-in
    3) Can support basic unpivots such as this
    4) Can support advanced unpivots with multiple header rows (say if there was a row above the header row)
    5) Is insanely quick

    I think it’s worth checking out: http://kb.tableau.com/articles/knowledgebase/addin-reshaping-data-excel & http://kbcdn.tableausoftware.com/data/tableau_data_tool.zip

  8. GET OUT OF MY BRAIN.

    Seriously, I spent the last few days fiddling around with a nearly identical problem… exploring (and not getting very far) with VBA solutions, THIS close to just giving up and manually massaging the source data to fit my need.

    The only problem is that this saved me so much time I don’t know how I’m going to fill the rest of the day. Beer, anyone?

  9. What a great article… I have been using (and promoting) Power Query internally at our company. Just because I want people to enjoy their beer!!

    Thanks for such a wonderful article!

  10. So I’m jumping to VBA’s defense here. If you’re finding VBA slow, then you’re notfinding the right VBA.

    Here’s a point-and-click routine that I prepared earlier:
    http://dailydoseofexcel.com/archives/2013/11/21/unpivot-shootout/

    (There’s quite a few code blocks at that link. The one you want is just below the words —Update 26 November 2013— at the bottom of the post.)

    It will knock the socks off that Tableau add-in for starters.

    Note that this code is extremely long and complex because it uses two different methods to smash those crosstabs: A really short and quick method in the event that the resulting flat file will fit in Excel, and a much slower and more complicated method involving ADO/Recordsets in the event that the file needs to be turned directly into a PivotTable on account of being too long to fit in the grid.

    In fact, Jon Peltier went as far as to call it convoluted. But who cares…it’s written, it works, it works fast, it works fast right from Excel’s grid, and it works fast right from Excel’s grid on all versions of Excel, and not just the premium version, like PowerQuery.

    It will handle a 53 Column x 2146 Row crosstab (which gives a 117,738 row flat-file) in just one second. Right from Excel.

    And it will handle a 53 Columns x 19,780 Row crosstab (giving a 1,048,340 row flat-file i.e. practically the biggest sized crosstab that you can unwind directly to Excel’s grid) in 19 seconds.

    And it will even handle larger crosstabs that would be too long to fit in Excel’s grid when unpivoted, by unpivoting them direct to a PivotTable.

    No premium version of Excel required.

    Just steal code, point, and click. These screenshots show it ain’t too onerous.

    Here’s the before:
    http://dailydoseofexcel.com/wp-content/uploads/2013/11/CrossTab.gif

    Here’s the during:
    http://dailydoseofexcel.com/wp-content/uploads/2013/11/20131119_UnPivot_Select-Entire-Crosstab.gif
    http://dailydoseofexcel.com/wp-content/uploads/2013/11/20131119_UnPivot_Select-Left-Column-Headers.gif
    http://dailydoseofexcel.com/wp-content/uploads/2013/11/20131119_UnPivot_Select-Crosstab-Column-Headers.gif
    http://dailydoseofexcel.com/wp-content/uploads/2013/11/20131119_UnPivot_FieldName.gif

    And here’s the after:
    http://dailydoseofexcel.com/wp-content/uploads/2013/11/20131119_UnPivot_Output.gif

    Don’t get me wrong: I’m not down on PowerQuery. But I’m not down on VBA. VBA is in fact a better solution for most people, because most people don’t have Premium Excel and therefore can’t use PowerQuery. In other words, VBA is the only solution for most people. And it’s not a BAD solution compared to PowerQuery, either.

    The real question is this: Why haven’t MS provided this pretty basic functionality to ALL excel users, rather than just a select few. Having to install PowerQuery just so that you can unpivot data using ‘native’ functionality is a ridiculous situation.

    By the way (shameless plug here), this is exactly the kind of stuff I cover in my soon-to-be-finished book Excel for Superheroes and Evil Geniuses. Unpivoting is exactly the kind of functionality that applies to the ‘Evil Geniuses’ flavor of the book.

    Can’t wait to see your replies.

    1. Hello Jeff,

      Regarding that Power Query availability requires a premium version of Excel, this will not be the case for long.

      On Chris Webb’s BI Blog, while discussing Excel 2016 in general and Power Query specifically, he says that

      “…[in Excel 2016 Power Query is] not an add-in at all, it’s native Excel functionality. Indeed you can see that there is no separate Power Query tab any more, and instead there is a Power Query section on the Data tab instead”,

      https://cwebbbi.wordpress.com/2015/03/17/whats-new-in-the-excel-2016-preview-for-bi/

        1. The following Office versions are supported:
          Microsoft Office 2010 Professional Plus with Software Assurance
          Microsoft Office 2013:
          Power Query Premium: All Power Query features available for: Professional Plus, Office 365 ProPlus or Excel 2013 Standalone
          Power Query Public: Available for all other Office 2013 Desktop SKUs. Includes all Power Query features, except the following ones: Corporate Power BI Data Catalog, Azure-based data sources, Active Directory, HDFS, SharePoint Lists, Oracle, DB2, MySQL, PostgreSQL, Sybase, Teradata, Exchange, Dynamics CRM, SAP BusinessObjects, Salesforce.

  11. Thanks for all the feedback, really appreciate it!
    As to the debate on VBA vs PQ; in this instance PQ is IMHO the winner, however, VBA is probably the best friend anyone can have when it comes to facilitating (ab)use of Excel. Rest assured that, eventually, we will get to the point where non of these will be needed as we will all be following the white rabbit… Whooaaaa 😉

  12. As someone who has used both PQ and VBA a lot, and have converted many UDFs and other macros from VBA to PQ, I can assure you that VBA has been faster than PQ in every single case.

    I have my issues with VBA – it’s old, it doesn’t support structured error handling out of the box (you can work around this issue to some extent), doesn’t support modern form controls, has a lousy security model, and so on. However, it gets the job done.

    I love PQ to death – when comparing a problem solved in PQ and VBA, the VBA solution is quite clumsy. Of course, VBA can solve problems that PQ cannot even begin to tackle.

    Now that PQ is built into Excel 2016, it would be great if an IDE is provided for M development. Minimally, the IDE would support building UDFs that would work in worksheet cells and continue to work online. In VBA, you can write one line of code for a UDF (e.g. ISDATE, LIKE, BANKERSROUND), and your UDF doesn’t work if macro security is off, of if you publish your workbook to SharePoint (locally, or online).

    In a PQ IDE, I would go one step further and add functional reactive functions, to provide event handling, UI creation and so on (not to mention the ability to access the Excel OM and other referenced objects). The goal would be to have the ability to use PQ as a complete functional alternative to VBA.

  13. WMPAROOTH? No google result and i’m not wasting time trying to figure it out. (per this article, when in doubt ask).

Leave a Comment or Question