PowerQuery assumes tabular data where each column is the data type and each row is a data element / entity. It is structured similar to a database.

In a spreadsheet the data is much less structured which is where a lot of the power comes from - for instance PowerQuery doesn’t really support things like subtotals easily, or doing scratch-calculations, or building quick financial models. It is closer to a paper-ledger with calculations scribbled into the margins than a big-data database.

PowerQuery is more about ingesting lots of data and cleaning it, while finance is often about working stuff out and playing with numbers to see what happens - and playing with numbers is easier in a less-structured-loosely-typed environments.

>PowerQuery doesn’t really support things like subtotals easily,

Subtotals? I was used to using GROUPING SETS with Oracle SQL, and found I could roll my own in Power Query. It's a good example of exactly why I like it.

Also, Power Query doesn't prevent you from using the regular table total feature or a pivot table based off of the Power Query output.

That is, even if Power Query doesn't provide all the subtotaling features you'd like in the way you'd like, it doesn't restrict you from anything, does it?

> or doing scratch-calculations, or building quick financial models

I do use it to do all sorts of ad hoc calculations - for instance, it can ingest PDF files or HTML with tables.

It sounds as if you're saying it's too complicated for really trivial calculations?

> It sounds as if you're saying it's too complicated for really trivial calculations?

I'm saying it's not the right tool for some classes of calculations.

For instance I work in designing warehouses, and use both tools. Here are some use cases where Excel doesn't do well and I would use PowerQuery:

* Ingesting millions of historical orders

* Handling relational data

* Data cleaning and aggregations

Here are some example use cases where PowerQuery doesn't work as well, but Excel is perfectly good:

* What height should the pallet racking bays be in this warehouse, and how many pallets am I likely to fit in the building envelope? (considering my other space requirements)

* What's the likely transport impact of opening a new distribution point?

* Running lots of scenarios or sensitivities.

Why are these better in excel? Well there are just some things PowerQuery doesn't do well, for instance excel can take into account any other arbitrary cells value into it's own calculation, while in PowerQuery you generally have to use an intermediary table and joins to handle this.

Can both tools physically do it? Yes, it's just some problems suit one rather than the other, and identifying the right tool for the right problem saves you lots of time. One thing that makes Excel better for scratch calculations for example is the fact that it's a live environment (with PowerQuery you have to run it after changes to get the results back, and this can be really slow compared to excel).