Power Pivot versus Power Query: The battle is on!

You have the opportunity to shape my upcoming talk at PASS BA Conference on Power Pivot versus Power Query. Yup I am going to pit the two loves of my life against each other. The premise of the talk is a question I get all the time, "Do we use Power Pivot or Power Query?" - asking about the approach to take when building Power BI Solutions.

(Note: This applies in Excel as well as Power BI Desktop world, even though in PBI Desktop the tools are not labeled as PP & PQ).

The answer to the question, of course is - You use both!

• Use Power Query to clean, shape and transform your data
• Use Power Pivot to model your data and define calculations

That is sage advice, however both of these tools are so capable that they do step on each other a bit. I have run into multiple scenarios where I did have to ask the same question to myself? Do I solve this using Power Query or Power Pivot?

And I plan to cover such scenarios in my speech. I provide the rough outline of some of the scenarios I am considering below. Here's where you can help:

Submit your own Power Pivot versus Power Pivot scenarios, and I'll credit you in my conference

So let me know if you have encountered some of the same scenarios that I have. Or submit your own. If you can provide with sample files, that'd make it more likely that I include it :-)

Scenarios

a) Flatten in Power Query vs. Use Relationships in Power Pivot

This is an easy one. I have seen users flatten tables using Power Query. This is the old-school Excel approach where all tables needed o be flattened. Instead of using VLOOKUP, now you can use Power Query. But, ugh! Why would you? When you have the magic of Power Pivot and relationships.

WINNER: Power Pivot

b) Bring in Flat Table using Power Pivot or Unflatten using Power Query

Aha! This is the reverse of the above scenario. Some times what you get is a flat table. In this scenario, it's best to unflatten using Power Query to create separate Data and Lookup Tables. To learn how to do this, see Chapter 20 - Power Query to the Rescue (Scenario #5 on Page 196) in our bestselling book Power Pivot and Power BI: The Excel User's Guide.

WINNER: Power Query

b) Extract filtered data using Power Query or Use Power Pivot measures with CALCULATE Filters

Let's say we have car sales data for all Make and Models. And customer states that they are only interested in "Honda". You can use Power Query to pre-filter the dataset to just Honda and only bring in those rows in your data model.

Or you can bring in the whole dataset, and then write a measure to only count Honda. e.g.

CarsSold = CALCULATE( SUM(CarSales[Units]), CarSales[Make] = "Honda" )

Almost always I prefer the latter approach. Why? Because I take what the customer is asking me with a pinch of salt. They may ask for "Honda" today but need "Toyota" tomorrow and "BMW" the month after. I try to build data models that can not only answer the questions being asked today but also the questions that may be asked in the future. Although I admit there would be some clear-cut scenarios, where it would make sense to just pre-filter using Power Query.

WINNER: Power Pivot. Although Power Query wold be better in clear-cut scenarios.

c) Time Intelligence: Year-to-Date, Year-over-Year comparisons etc.

Every time I see people doing time intelligence using Power Query a part of me dies. Power Query is very flexible, and it would let you do things like that - add columns which calculate Year-to-date totals or show the previous year amount.

But this approach is inflexible and wasteful! Power Pivot is extremely strong in Time Intelligence. I have not counted it up myself, but I have been told that Time Intelligence Functions has the largest number of functions than any other DAX function category.

Time-Intelligence is a slam-dunk for Power Pivot. Check our many Time-Intelligence articles on PowerPivotPro.

WINNER: Power Pivot

Conclusion

Okay, you might spot a trend here. It might look like I have a bias towards Power Pivot. That's somewhat true. Power Pivot is my first love. Power Query came along later and also swept me off my feet though. I do have scenarios where I have found Power Query to be a clear winner. But they are a bit nuanced to explain in a short article (and I am afraid in an hour long Conference speech).

Have you run into scenarios, where you needed to ask yourself - Power Pivot or Power Query? Were they any that I list above or new ones? Either way, let me know. If you go the distance of providing sample files and I present the scenario in my PASS BA speech, I would make sure to credit you.

Power On!
-Avi Singh
Follow me on LinkedIn