Power Query load to Pivot table vs Adding to Power Pivot Model

Question

I'm struggling to understand differences from a user POV between Power Query and Power Pivot. I get that they are optimize for different things, and that the can work together but on a practical level I'm not seeing a difference.

For example, the difference between:

1. taking power query connections and loading them to a pivot table vs
2. taking power query connections and adding them to power pivot vs
3. making a power pivot connection directly to a source?

From my point of view

1. If I connect to a source with 10s of millions of rows, do some stuff, and load to a pivot table things seem to work just fine. I also have the nice features of pivot tables available handy and I can do pretty much anything else I need within power query itself. This seems the best of all worlds to me.
2. If I take that same source and add it to the power query data model, then I have to wait and reload all the data to the data model (so double the time) and I lose the easy features of pivot tables (i.e. I have to use measures instead of fields which isn't that bad).
3. If I take power pivot and try to load directly from the source, then I lose a lot of the transformation capabilities of power query.

So, from my point of view I'd never use power pivot. What am I missing?

Answer 1

I think you're confusing a couple of different concepts.

Power Query is simply a transformation tool which takes an input, performs some transformations and then spits out an output. In Excel that output can be sent to an Excel table or to the Power Pivot data model. In Power BI, PQ sends its output to the PBI data model.

Power Pivot is effectively the columnar database within Excel (which uses the Vertipaq engine - the same one in PBI). It takes the data from PQ and highly compresses using dictionary encoding, run-length encoding etc. You can also create relationships to further reduce the amount of data you need to store for a particular piece of analysis.

There are use cases for using both. For instance, if you want to pipe 10 million records into Excel, the only way to do that is to send it to the Power Pivot data model. What's more, depending on column cardinality, the size of the .xlsx will be tiny compared to storing those values raw in an external csv file.

Another silly example but one I use all the time is the ability to do distinct counts in a pivot table. Unless you load the data to Power Pivot, this is not possible with a standard pivot table.