skip to Main Content

In First Impressions, Power Query Steals the Show

Let’s say I have ten minutes with an “uninitiated” Excel pro – someone who slings VLOOKUP and Pivots all the time, but has no idea that Power Pivot and Power Query exist.  And in those ten minutes, I’ve got to quickly demonstrate the capabilities of “Modern Excel” or Power BI (where the former means “Excel with Power Pivot and Power Query”, and the latter contains both of those same technologies).

M “Sets Up” DAX. And then DAX Spikes Success All Over the Competition.

M “Sets Up” DAX.  And then DAX… Spikes Success All Over the Competition.
(If Will Ferrell ever made a volleyball movie, I could imagine him saying something like that)

I’m going to inevitably spend eight minutes on DAX, and then two minutes on Power Query at the end.  I favor that 80/20 split because I think that’s a realistic picture of your future as an Agile BI Pro:  80% DAX, 20% Power Query/M.

But for the uninitiated, Power Query is always the sexier demo.  Always.

Why is that, and how do I “square” that with my 80/20 ratio, and my stubborn insistence to not “lead” with the sexier demo?

First Impressions “Follow” Existing (Traditional) Workflows

It makes total sense to me actually.  Traditional (pre-DAX, pre-M) Excel workflows always have involved a heavy dose of manual data munging.  Squashing a folder full of CSV’s together into a single table, for instance, was just as much “a thing” in 2002 as it is now, fifteen years later.  If you can reduce that to a few clicks, AND then automate all subsequent “runs” of that work down to a single click, wow, that’s mind-blowing to longtime Excel users.

So the magic of Power Query is instantly apparent and tangible to basically any Excel Pro.  They can immediately see how PQ will save them oodles of time and anguish.

The benefits of DAX and relationships, by contrast, are less readily-apparent on first glance.  Portable/re-useable formulas that enable rapid iteration, the answering of “emergent” questions in near real-time, as well as a “subdivide and segment” capability?  Or how about multi-data-table capabilities that provide an integrated and convenient view across many different sources of formerly-siloed data?  These concepts are simply alien to the longtime Excel user, even though they are MONSTERS in terms of their biz value (as well as time-savers and anguish-reducers).  None of the impact “lands” up front because it can’t adequately be contemplated until you’ve started DOING it.

“Help me do what I already do, faster” is FAR easier to absorb than “blow the doors off my existing world, unlocking entirely-new workflows.”

Power Query Outshines DAX / Power Pivot in Early Demos, but Long Term, the Power is in the Latter

“OK…  But if Power Query Sets Up DAX, Why Learn it Second?”

Yeah, fair question – PQ does get used FIRST, prior to DAX and Relationships, in any XLSX or PBIX file you create.  So, um, why do I say learn it second?

Here’s a slide we use fairly often, in which we illustrate the relationship between Power Query (aka the M engine) and Power Pivot (aka the DAX/Relationships engine):

Relationship of the Various Power * Techs

Power Query/M is a “Preprocessor/Cleaner” for Data Before it’s Fed to DAX

So let’s say that Power Query “feeds” the DAX / Power Pivot engine.

And Power Query can “make” just about anything (in terms of output data shapes) – let’s start out using cake, burgers, croissants, and pizza as a demonstration of its culinary range…

Power Query Feeds Power Pivot / DAX

Well then…  what does the DAX engine like to eat?  Shouldn’t we get to know it better first?  What if it has dietary preferences, allergies, or other particular needs?

Power Query Feeds Power Pivot / DAX

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.

“A bit overwrought in the metaphor department, perhaps?”

Sure, fine, but let’s ride it to its conclusion, if for no other reason than we employed a lot of clipart in the making of this post and clipart needs jobs, too.

Here’s the gist:  if DAX is where the transformative power truly lies (which is true), and it has strong preferences about the shapes of data you feed it (also true), we need to understand that before we can use Power Query / M properly.

And some of the things it likes/prefers might NOT be things we’d even think to “cook” (hence the coffee example above).  So, we need to get a feel for what DAX likes to eat.

“Do you really mean I should master DAX and then start using Power Query?”

No, nothing so extreme.  What I’m really cautioning about are the dangers of an insidious disease, Queryus Infatuationus, that tends to strike adopters of Modern Excel and Power BI in their early days of usage.

In short, the malady is this:  “look, I’ve got this cool new M hammer!  It’s the answer to my dreams and clearly I should use it for everything!”

I’ve seen M used extensively as an analysis tool, for instance, and that is Not What You Want to Do.  Using M to pre-aggregate and analyze data (instead of DAX), is akin to using SQL for that same purpose (also instead of DAX), and I’ve written about the latter before.  Short version:  you sacrifice all those lovely benefits of portable formulas I mentioned at the beginning of this article (iteration, answering emerging questions, subdivide and segment, etc.)

Bottom line:  in the early going you are going to be learning both (unless all your data lives in databases, and the admins of said databases are willing to make changes for you – in which case you may not need Power Query / M very much).

I’m suggesting, specifically, that you should advance to Intermediate skill in DAX before advancing to Intermediate skill in Power Query / M, and then to Mastery of DAX before Mastery of M.

Another take on this:  if you’re learning to write M scripts from scratch (as opposed to getting by with the toolbar buttons and the occasional google search for a snippet of M), and you haven’t yet conquered CALCULATE, ALL, and FILTER, you’re probably getting too deep into M at the expense of your DAX skill.

Parting Disclosure

I think it’s only honest at this point to say that “getting by with the toolbar buttons and the occasional google search for a snippet of M” is exactly where *I* am at personally with Power Query / M.

On one hand, you may read that and say “oh great, he’s just advising us to be like him so he doesn’t feel bad.”  Even I wondered about that a bit, while writing this article – I was on the lookout for my own confirmation bias because my inner critic doesn’t stay inner – he sits on my shoulder while I write.  And I’m very much aware that at my own company, I rank in the bottom 10 percentile when it comes to M.

But on the other hand, I’m very confident in my assertion that M is not the ideal producer of final results – you don’t want to feed M output directly into visualization layers.  The bar charts at the top of this article paint a proper picture – you don’t get any of the Ten Things Data Can Do For You until you learn how to wield DAX proficiently.  So you absolutely are sacrificing TREMENDOUS capabilities if you leave DAX out of your equation – even if “leaving it out” simply means “not fully leveraging it.”  And if you ARE fully leveraging it, it has a “say” in what you should be doing (and learning!) with M.

Go get ‘em.

Rob Collie

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 50 Comments
  1. Thanks, Rob!
    I love the thought provoking nature of the article… “food for thought”, if you will.

  2. Very timely post Rob. Just gave a Power Query/DAX/Power BI training yesterday and the comments coming from the group about Power Query and M matched your “sexy” comment. They can definitely relate to the workflow of their current job responsibilities. I use the metaphor of Power Query / M to plumbing and electricity. You have it done once (hopefully) for your house and then you kind of forget about it until an issue arises or you need to remodel. DAX is comparable to that nice new kitchen (basement, bathroom, fill in your room of choice) and all the fancy appliances you can plug into…

    1. Jason I find your comment particularly gratifying since your question years back is what triggered me to write the “I know SQL” post!

      Your powers have clearly grown, the Force is strong in you.

  3. I strongly agree, even though a deeper knowledge of “M” might be necessary when your data sources are too far from ideal and your IT department won’t help (or doesn’t exist), even for “simple” data models.

    1. Good point. Necessity is not just the mother of invention – it’s the mother of everything!

      My caution is just that, for instance, a devotion to M before learning DAX (and modeling) might lead you down a road where you squash everything into one huge pre-aggregated Frankentable before feeding it into Power Pivot or Power BI, and you wouldn’t even know what you were losing out on.

  4. I couldn’t agree more.

    When I taught a full-day Power BI class in 2016, I ended up teaching it “backwards” for exactly this reason. I started out showing Power BI reports, because modeling without a clear idea of what you’ll be able to do with the end-result can be very abstract & difficult to grasp. (Not everyone has a pivot table background.) Likewise, I showed Power Query/M last, because, well, because of all the brilliant reasons you & your clipart dinosaur have outlined.

    Stephen Covey once said “Begin with the end in mind”, and that definitely applies to learning Modern Excel/Power BI.

  5. So… just to throw a counter point out here, which you probably expect from the guy that wrote the book on Power Query… I’ve seen many users who have learned much DAX and try to use it as THE tool to do their transformations. It doesn’t go well, ending up with DAX measures that are pages and pages long, nested to levels of complications that are next to impossible to debug. It’s frequent and it’s ugly. That’s my biggest issue when watching DAX pros is that they fall into the “every problem is a DAX problem” when it’s really a crappy data shaping problem.

    Once Power Query enters the situation, the data can be cleaned and reshaped in advance, resulting in DAX measures that are only a few lines long. Simple CALCULATE measures with only a single SWITCH or IF statement included.

    Personally, I advise Exce; users to learn PQ first, only because it’s bigger than just Power Pivot. It’s useful for non-pivot tasks (like aggregating folders full of files), merging data in ways that you can’t without it (identifying non-matching items, for example), un-pivoting data and so much more. And all of that is 100% UI based, not a single line of nasty M code to be written. I have used Power Query in this way many times, never landing a table in the data model at all for some solutions. (That feels very weird to say on this site, but it’s true.)

    When it comes to Power BI classes, my philosophy is different, only because everything always ends up in the data model. In Power BI, I focus equal time on each tool for this very reason. Power Query and DAX complement each other and work better together. Neither should really have preference over the other, IMO.

    1. I’m a M and DAX newbie who moved from an accounting role to a BI role a few months ago and have been teaching myself with the help of both your book and Rob and Avi’s book. My organisation is using tabular models restored from Powerpivot workbooks with Power Update, and I’ve been doing the ETL and the model build. It is sometimes confusing to know whether I should be using M or DAX functionality. I reckon you guys should collaborate on a book covering both topics 🙂 Love both your books and your blogs – sooooo helpful!

    2. Hi Ken!

      It may sound like a detail, but for us, it’s not: our company doesn’t really “do” normal Excel work. We’re only involved in analytics and reporting and BI and whatever else the industry wants to call it these days. (EX: you don’t see any articles on this site about array formulas, and you don’t see any VBA unless it’s somehow used to automate a Power Pivot model).

      All those things you mention like squashing folders of files together and unpivoting are EXTREMELY useful to us in the analytics space! But what’s the REASON for wanting the unpivot, or the remove duplicates, etc.? It serves a modeling purpose – at least in the world where PowerPivotPro LLC earns its money.

      As Marco points out in a subsequent comment, it may be best to differentiate between DAX (the formula language) vs. the art of modeling (what should your tables be, how are they related, how are the tables shaped), which is why I tried to appease the purists and include “(And Modeling)” in the title of the article. Combined though, those two things (DAX and modeling) are the definition of Power Pivot, and when it comes to teaching Excel users how to enter this new world… the distinction isn’t quite so clear to them. It’s all just new.

      I think we agree that DAX ain’t the way to do an unpivot (cuz, you know – not even possible). But DAX (and modeling) are the REASON to do it, and until you “get” that, you might spend a long time barking up the wrong tree, doing the “wrong” things in M.

    3. Totally agree with this. Why create the ‘Sistine Chapel’ of a DAX solution when you can do the heavy lifting before it gets to the data model? I think the ability to select one or the other effectively comes from experience. At times it feels like there is simply too much choice and options but what a great place to be!

      1. Heh heh, I’m not here to build the Sistine Chapel, I’m here to burn down the very idea of such things 🙂

        As clarified in another comment, I think some folks are reading “Learn DAX/Modeling FIRST” and thinking I meant “Learn DAX and don’t bother with M.” I mean the former and not the latter.

        But hey, it’s drawn some of our finest community members to the comment section! So… some positive tradeoffs too 🙂

  6. umm yeah. PowerQuery is easy! Like junk food, so good clip art. What PowerPivot can do is so much more and I ‘ve seen the weaknesses of just regular Pivots. I sooo want to get DAX in my brain. Whether it’s P3’s demos or Microsoft’s demo’s I see the value.
    What is unfortunate is the difference in use and learning curve of these two PowerResources. PowerQuery is refined Data Import (familiar) where PowerPivot is more DataBase (SSAS) and that’s a tough one for the “flat-filers”

    (Don’t you hate it when the managers only want flattened data ? !!)

  7. Great post Rob! And I couldn’t agree more that learning DAX brings more overall value in the long term. I do think there is one other roadblock which corners Excel data pros into focusing more on M rather than DAX. In my particular case, in designing reports for external consumption, I’ve unfortunately had to resort to using M more than DAX due to the fact that these end users don’t all have the appropriate Excel add-ins/versions to utilize DAX.

    To get around this limitation, I’ve designed reports using M to import and shape data and then load data into tables within the workbook, since all users/excel versions support tables by now. I can then use standard Excel formulas to report off of the tables. This has led to more automated design/import of data and distribution, as well as analysis, however I agree it barely scratches the surface of what can be done from a reporting perspective.

    Hopefully as the Excel Revolution continues I will look back and this will be a problem of the past. Thanks again!

  8. Provoking, yes. Agreed, you need to understand what & why the result of M has to be BEFORE applying transformations. What is the model and how it will be manipulated by DAX.
    In my practice time consumption is opposite – 80% of M and 20% of DAX because of pure mess in sources (wonderful CSVs? A dream…). But I can see that when I have DAX in mind, uses of M become more… Correct? Straight? Accurate?

    I can see also a lot of attempts to make almost all calculations in PQ, and understand why: when you used to hours of manual mashing up the data, M looks like magic wand. It’s normal for neophytes.

  9. Great article on “M” vs “DAX”… I think my favorite part was the “paleo” diet with the dinosaur… *Slow clap* Well played Rob, well played indeed 🙂

  10. Rob,

    Excellent point! Agree with you 100%. It is hard to think of more productivity (power pivot) when you get a tool (power query/M) that can free you from the grip of one of the biggest time wasters ever: dirty data. It took me a little while to grasp that–with features like formula re-use, built-in time intelligence and centralized formatting–Power Pivot would deliver me from many other nagging time consumers.

  11. Queryus Infatuationus! A term that will live on through the ages. Love it. 😉 Thanks for the article, Rob. This is valuable in keeping my own learning path in perspective — and importantly, how I frame the topic for others when I introduce it to them.

  12. I tend to go with Ken Puls on this one. I use Power Query for all kinds of things beyond data analysis that never sees a model. Cleaning and formatting data for an upload into another system, for instance. I do agree that knowing what format DAX needs before building your tables with Power Query is important (and something I wish I’d done that way when I first started), but while DAX is much, much better for data analysis and M just feeds it, Power Query has so much broader applicability that I think it’s impossible to overstate it’s usefulness. There are a literal handful of people in my office who ever need to do the kind of data analysis that DAX allows, but there are several dozen who routinely do transformations that Power Query can (and slowly, is!) handle for them.

    1. No resistance here to what you are saying, AT ALL – plz see my reply to Ken.

      And that literal handful of people you’re talking about? Those are the folks we help.

      And, if I may be just a little bit forward here, I’ll also say that those analytics tasks are where the majority of biz value opportunity lie. Which explains why a humble blog started in 2009 is a rapidly-growing company today.

      Career growth. Money. I say this not to be confrontational, but sincerely to be helpful. Anyone who’s reading this (including you) are MY KIND OF PEOPLE and I want you to be adequately validated and fulfilled 🙂

      1. Sure, from PPP’s perspective this all makes perfect sense! I just think you’re underestimating the breadth of people getting into the Excel Revolution beyond analytics that read your posts, especially as I frequently refer people to it (grins). And as Imke says below (much better than I can), PQ is an excellent robotic housekeeper that can save you a lot of time which you can then use to learn modeling. I’d take it a step further and say that for people like us, it can let people who used to come to us for routine automation solve problems without looping us in and save even more time that we can then put toward greater insights. I’m just finishing up an analysis on an RFQ in Power BI that’s very DAX and modeling heavy, doing calculations and visualizations that we couldn’t have even dreamed of two years ago to make the numbers case stunningly obvious, but I was only able to do that because PQ had simplified routine automation for so many different things that I could be freed up to work on it without the office slowing down.

  13. Just adding my 2 cents…
    Model first, DAX second. The more you solve using the model, reducing the DAX surface, the better.
    And getting a good data model usually requires… massaging data – where Power Query / M is second to no one.
    As usual, it’s necessary to find a good balance.
    Creating the final report with many Power Query / M transformation: wrong.
    Transforming data in DAX: wrong
    Massaging data in Power Query / M to create the “right” data model (one entity, one table): right
    Leveraging the model in DAX (lower the iterators, reducing context transitions): right

    Any extreme will drive in a non-optimal situation.

    1. Thanks Marco! I sincerely think we’re saying the same thing just in different words. I agree, I could have left DAX out of my article title and just called it Modeling First then M. That would have been more strictly accurate for sure.

      My intent here is to reach the folks who are early in their “graduation” from pure Excel to our toolset. And for them, the term “modeling” doesn’t have much meaning. It’s all just… new. The line between the new formula language and the concept of modeling isn’t that sharp for them, so I’m somewhat deliberately conflating them.

      1. Yes we agree – and there is a reason why I and Alberto wrote a data modeling book for Power BI and Power Pivot without using “data modeling” in the title!
        Your post will certainly help to discover that world to many people.

        1. Great discussion in the comments! I agree with both of you in creating a solution that ends up in the realm of Power BI.
          I believe Ken was the referring to the far bigger picture of Power Query for the vast majority of Excel users and how it can benefit you out of the Power BI realm as a standalone tool. Since we call this the Excel Revolution, PQ adds something to this revolution that PP didn’t and that is basically working outside the boundaries of a Pivot Table with your end result in a really simple way. I can’t tell you how many people I’ve met that have completely stopped using VBA for ETL processes and are now using PQ for its flexibility and ease of use – this is part of the Excel Revolution that Ken is talking about.

          Over the past 2 years I’ve been putting Power Query through extreme conditions trying to understand its limitations and also the right balance between using just Power Query and adding Power Pivot to the mix.

          Sadly, the comparison is one of those case by case scenarios where it really depends on a number of factors, but sometimes using just Power Query can be the right choice (if done right). Sometimes, if you want to add Power Pivot to the mix you’d need to create tables specifically for the Data Model that DAX needs and those could take more time to load/process than just one single result from a query. Another scenario is perhaps that the end result that the end-user would like to see in a pivot table has far too many column fields and you know how crazy problematic that can be for performance. It also depends on how they’d like to use that result – do they want to use their regular Excel formulas against the end result? (some formulas that simply don’t exist in DAX)

          Again, its one of those case-by-case scenarios, but I completely understand that this post is aimed towards Power BI enthusiasts rather than the overall Excel audience – even at particular times is better to create a DAX Table rather than using Power Query to create it because of server performance and how sometimes PQ doesn’t work on cached data because of parallel processing.

  14. Another top post from Rob, just when I need it… And guess what: tomorrow I am training a whole team on PP and PQ

  15. When I get contacted by someone asking for help, it normally goes “can you help me with this DAX formula…”. But when I look, they really need data shaping using PQ. As Marco said, get the shape right and the dax then comes into its own. But I am also amazed at how many people come to my training classes (ie they have found PP and/or PBI) but have zero knowledge of PQ. When they see it they are generally blown away. it is definitely much easier to excit newbies with PQ

  16. This is a good article because it addresses an age old dilemma.
    After 30+ years of using so many different tools, from VisiCalc to Excel, dbase to sql server, Delphi Pascal to C#, and HTML to JavaScript, whenever deciding on the right tool to use, it generally comes down to “It Depends”.
    However after so many years, you can get a feel for the right tool in solving problems. In my experience it is generally: “The problems are the same, just the data is different.” 🙂
    It also may depend on prior experience. I never was an excel guru, more a database guy. So I took to DAX and the data model quickly. And the code reuse is awesome. But I have to say I started using power query more and more, as I became aware of its excellence in preparing data.

  17. I like your explanations! I am still trying to get my head around the difference between PQ and PowerPivot (DAX), and when to use. Formulas between Excel and DAX and M are quite different and new to me.

  18. Another good article Rob and I can see your point. My experience has been that Power Query has a much broader base of applications and not just Power Pivot solutions. Work I used to do with clients using VBA is now being done in a fraction of the time with M.

  19. This clipart is so awesome. That had to be said.

    But to get back to content… understanding the data model is critical (this is a requirements gathering exercise). Power Query does lots of things –and there is so much functionality in this tool, it’s crazy– but the transformations do not always go into a data model. Power Pivot DAX are always needed to for pivot table aggregation analysis. The Antique Excel users (I guess that’s the antonym of Modern Excel) will get zillions of benefit from knowing PQ. The “staticians” (maybe there’s fewer of them?) can rock some socks off with DAX.

    So I would echo Ken/ Marco/ Matt. That PQ/M has more use cases.

  20. I would also have to agree with Kun & Marco. I started out just using Dax and no PQ and it was not pretty. I needed to do things the model wasn’t conducive to and the Dax was horrendous. Once I found my way to PQ, my DAX life got much easier. I just can’t imagine going without either one now. But totally agree: Model first, Dax second. Nobody is spiking the ball without a good setter.

  21. Is there a recommended data set for initially exploring Power Query (M) and Power Pivot (DAX). I think PQ is mainly to bring in and shape data, then once that’s done, use DAX to crunch the numbers. Do you think Excel and M and DAX will be harmonized or same standard some day?

  22. I built a company budget that used Power Query to shape data from our business forecasts. At the end of the budgeting process we had a “Budget” a “Revised Forecast” and “Actual Trade” all merged together through Power Query and presented in Power BI… The DAX contribution was, Sum(), Divide(), YTD(), Sameperiodlastyear() and some other variations. I guess the lesson is, get the data structure right and the math is easy.

  23. Very much agree that to fully enjoy the sexy analytical capabilities that the new tools bring, it’s essential to know how to feed your VELO (ciraptor / city-engine) right.

    However, a lot of us accountants can get very excited about new housekeeping tools. You can do much more with PowerQuery and M than just to feed the Velo. Tasks that end up in booking entries being automatically interfaced into your accounting system, regular reconciliations made possible with just one click, clean up all sorts of VBA mess… (like Wyn mentioned). For many accountants, it could make sense to learn PowerQuery/M to train their new robotic housekeeper first. This could actually free them up the time they need to learn DAX.

    That being said – a good foundation of datamodelling also helps tremendously to train you housekeeping robot. So my advice also is to start with that first. If a business person doesn’t feel enlightened when first reading a good book about datamodeling, he probably doesn’t have “the data gene” (as Rob often calls it). It will probably take them more time than they can spare to learn what’s needed to become self-sufficient with DAX.

  24. DAX has been my hammer first in PP and now in BI. It was the perfect next step for me in excel. It was easy to start and get going. I second jumped over to M, read the book, and had a hard time w M. Thought I’d get by with the menu and learn from the formula bar.. I really haven’t. Since, I’ve read a couple things that are going to get me back in the M book again very soon.

    1. I can leverage M to set up my static data.. why have DAX do all the work every time, when I can spend more time in query to clean and CALC columns? It will also be data included in the compression.

    2. I read a function in ch 5 of Alberto’s new book that made me wonder how many other M functions I could learn to prep my table even further. Someone needs to come up w a M tip card!

    I don’t need to write M from scratch, however I’d like to understand the syntax a little better; most of all I’d like to have a good number of M tools to be aware of when creating a model.

    Thanks to all you guys writing these books and courses!

  25. Is learning Power Query / M before Power Pivot / DAX the normal approach?

    Going back before Excel 2010, we had been using SQL and VBA to cleanse data (except in the truly awful environments where it is all a big mess of VLOOKUP…).
    It is very challenging to develop a business case to move away from Microsoft Query and macros into a M based query.

    It’s a lot easier to demonstrate the benefits of the Excel data model with Power View and slicers; which requires solid DAX expressions in most cases!

  26. @Rob – DAX is difficult initially especially if you are an Excel Pro – Because for a long time during your journey in DAX you are going to say – “Hey but this can already be done in a Normal pivot” – Just right click on a Filed “Sum of Sales” and go to show values as – and look at the options – % of Column Tot, % of Parent Total, Running total, % Running Total , %Difference from etc. – Too simple in Excel – and far more complex in DAX – So initially you begin to wonder whats the point – Its only after you start creating measures like New Customers or Returning customers or Lost Customers or when you compare budgets v/s actuals at a product category level and can expand a pivot to show actuals at a Product level and blank for budgets – that you finally realize the Power of DAX – The “EUREKA” moment takes a long time to arrive.

    The next major hurdle is understanding DAX – try explaining one of your student why
    =CALCULATE(Countrows(DimProductSubCategory), DimProduct) returns 1 if the pivot is filtered for a Single product
    and suddenly your student is going to jump and say “Hey you said Filters flow DOWNHILL all along…
    ( may be you should write a blog post on this one 🙂

    Power Query is WOW from the word go – you see its value immediately – but then people start using this to create that “ONE BIG WIDE TABLE” which has all the Columns that you need for your report and then Data “Modelling” goes for a toss.

    The other key difference are the teams

    The Power Query team is the very definition of the word “Agile” – We have at least 3 new things happening every MONTH !!!
    The Power Pivot team – is the traditional Microsoft Team – What was the last major thing that changed in the Data Model – “Bi-directional” relationships – and that hasn’t arrived in Excel 2016 till date.

  27. Rob’s post has inadvertently summoned the Council of Elrond for the Excel Kingdom. I am fortunate to bear witness to each testimony and proffer my own with humility and respect. The M vs. DAX debate is something of a false argument. They are both essential tools. I liken the Modern Excel environment to designing, building, and piloting a starship. In Excel this corresponds to data modeling, M, and DAX. For those only needing an ETL tool to replace/augment VBA., M fits the bill. For a BI solution though, all three are needed and can be learned simultaneously starting with small projects and getting more complex as knowledge accrues. Based on my own experience, here is what I would recommend for studying:

    1. Read Rob/Avi’s PP book and Ken’s M book at the same time flipping back and forth and applying the knowledge to your own examples.
    2. Read Marco/Alberto’s new data modeling book to get a bigger picture of how this all fits together.
    3. Re-read Rob/Avi’s PP book and Ken’s M book to pick up everything you missed the first time.
    4. Finally, try to scale the Mount Everest of Excel BI books, Marco/Alberto’s DAX book, in the quest for true Excel BI mastery.

    Good luck and hope to cross paths along the way.

    1. Yeah I think there’s been an emotional reaction that I didn’t expect.

      I truly meant “learn DAX/Modeling FIRST,” not “DAX is better than M so you should ignore M.”

      FIRST. Not Only. Not even close. I love Power Query. It’s a gift from heaven.

      1. Yep, I got that’s what you meant. It was just your chart about the relative benefits of the two that brought out my desire to defend M and emphasize it’s utility outside of analytics. Also, on the next post I want to see clipart for the Council of E(xce)lrond!

  28. This is exactly what I am experiencing right now! Using Power Query at a basic level to feed my reports data as I ramp up my DAX experience. Doing this in conjunction with the online video course has been a great combo. Keep up the great work with blog!

  29. This is very timely. I had an employee who was learning the “power” tools and was hung up doing things in Power Query that could easily (and betterly) be done in Power Pivot. I tried to lead them down the correct road, but they could not get over the DAX hurdle in the short time they had to work on the project. Ultimately, the ease and intuitiveness of the point and click PQ environment completely overwhelmed the PP/DAX potential. Creating measures are a whole new mindset. You can use PQ without ever really learning M (and never learning the full potential of PQ). You can’t use PP without learning DAX.

    In the end, I would agree with Rob. Throw out M. Who needs it? Wait, that is not what Rob said. No, no. I do agree with Rob (and Marco, and …) that the model and what you are trying to accomplish are the priority. At that point, it becomes a tango between PQ and PP to accomplish what you need. It is almost like a witch’s brew (at least it feels that way to me – it is all magic!) in that you need a little bit of this and a little bit of that. and voilà.

    You mind is blown!

  30. PowerQuery lets me do things much quicker than I can now.
    PowerPIvot lets me do things much tidier than I can now.
    Being an antique Excel/VBA ninja, I can get by (slowly) without either. But I can see that both are going to lift my game considerably, and I also know that I don’t want to be left on the wrong side of the digital divide when these become (even more) mainstream.

    Currently I find I’m working damn slowly and damn hard to do things tidier in DAX than I can do using my old-skool approach. I can’t wait until I can speak DAX as well as I can formulas.

    But I can already get by in this strange new land with my conversational PowerQuery. I already know how to say the PQ equivalent of “Where is the bathroom” and “May I have the check please”. Because how PQ works, it’s like someone slipped a babel-fish in my ear.

    The me of now is struggling a bit with DAX. But the me of 10 years ago could pick up and run with PowerQuery immediately.

  31. Besides its universal applicability for automating the ETL process, a major thing which makes PQ stand out is its accessibility. PQ’s UI is truly brilliant, and makes the PP interface look like a half-baked product (if even that much). I think Microsoft has really dropped the ball on making PP accessible to a broader audience as an average person tends to shy away from actively coding, even if syntax is simple. Microsoft did an alright job with Excel by making pseudo-coding more accessible to people with a decent UI and classification of formulas by what they do. PQ is a great job. But PP? Why is it necessary to learn a chunk of syntax, and remember how to structure it before one can do anything useful or even basic? Especially when calculations in DAX can often be just cross-table calculations/referencing with maybe a bit of filtering or time-intelligence functions, it makes no sense to me that the PP UI is so…lacking.

Leave a Reply

Your email address will not be published. Required fields are marked *