“Do we use Power Pivot or Power Query?” – I get this question all the time when teaching Power BI classes.
(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, they are still very much present. See Excel Power BI / PBI Desktop comparison).
I would be presenting this topic at the PASS BA Conference (May 2-4). I would be fleshing this out in the next month, but wanted to solicit your thoughts and comments.
Have Your Cake and Eat It Too – Use Both!
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
Use Power Query to Get Data
On Power BI Desktop, you don’t even have a choice – the only route to connect to data is via the “Get Data/Power Query” interface. Which is A-Okay with me. Even with Excel, I now connect to ANY data using Power Query.
Use Power Query to fill all your Get Data needs
Yes, ANY data. Even if I could connect using Power Pivot to those data sources and did not need any transformation – I still always use Power Query.
I can think of few scenarios where Power Pivot would be preferred:
– Excel 2010 Users where Power Query cannot send the data directly to the Power Pivot Data Model (without a hack)
– User needs to publish their Excel Model to SSAS and then plan to run the refresh in SSAS (since it does not support Power Query refresh). Very edge case scenario in my mind
And there might be a few other scenarios. If you know any glaring ones, let us know via comments.
But in general (for Excel 2013/2016/PBI Desktop) Power Query is the way to go for getting data.
Use Power Pivot to Model Your Data
Relationships: Please! Don’t use Power Query to flatten data into a single table. Leverage ‘relationships’ in Power Pivot. In fact, if the dataset you are connecting to is “flattened”, use the magic of Power Query to un-flatten – separate Data table and Lookup Tables – and leverage relationships in Power Pivot. For this technique (unflatten), read Chapter 20 – Power Query to the Rescue, Scenario #5 Using Power Query to create a Lookup Table, from our book Power Pivot and Power BI. For general Data Modeling advice, read my earlier article, Data Model: Beast to Beauty.
Calculations: In general, I eschew doing any math/calculations in Power Query. I save that for DAX measures within Power Pivot.
Throwaway calculations, you need in order to shape your tables are obviously okay. Example, to trim your Product table to only Products which have a sale, you may add a column calculating sales for each product. But after the trim is complete in Power Query, you would just remove this column.
There could be few other scenarios where a numeric calculation in Power Query would make sense. Example, really intensive calculations (Ranking) may be manifested as a calculated column using Power Query.
But as a general rule of thumb, for any math/calculations I use DAX measures.
The Twilight Zone
All of 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?
Here are some of the scenarios I have encountered where I needed to weigh between the two. Let us know if you have encountered some of the same scenarios or any new ones.
a) 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” )
I typically 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 for clear-cut scenarios, it would make sense to just pre-filter using Power Query.
WINNER: Tie (It depends)
b) 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
c) Split Data Rows
This one was a bit complex – but the gist was – I needed to say take the Sales landing in OrganizationA and split them across OrgB & OrgC. I used Power Query to do this split and it was a reasonable solution. However, my concern was that if I was dealing with hundreds of millions of rows – it would result in a large increase in my row count.
WINNER: Power Query for small/medium data sets.
By using the right tool for the right job, you would end up with models that are clean and efficient. I will be preparing a lot more scenarios like above and fleshing these ones out for my PASS BA talk.
Meanwhile, let us know what your thoughts are around this topic.