Most people don't realize that Excel (like PowerBI) has an in-memory, compressed, column store database inside of it.

Loading hundreds of millions of rows into it takes a while, but given a commensurate amount of RAM and a reasonable data model (single table or star schema), performing aggregations with a pivot table is pretty snappy.

PowerBI is quite nice too.

Well, for me with my personal PC with a quad core and 24gb DDR3, it gets unusable for about 1 million rows, but it's true that most of my datasets have around 10 columns or so.

You should be using PowerPivot in Excel.

Here's a video showing rapid pivots of 100m row dataset

https://youtu.be/0WwFJ0Zg3d8?t=2285

Mmm, I was using PowerQuery and then use the editor to do the transformations (don't remember exact names sorry), and I just couldn't cope with it.