As someone developing essentially a competitor to Excel-and-PowerQuery/M, I find all this very interesting.

My language is strict and statically typed. However, after arrays (tables are arrays of records conceptually) exceed a certain length, rather than processing them in-memory as arrays, they will be offloaded to storage and processed (transparently) in a streaming fashion.

I’m surprised that this doesn’t work well in PowerQuery. I would have thought that 100K would be peanuts for it.

Mine is a SaaS however, so the user’s laptop isn’t a constraint, and I can transparently throw a million records in BigQuery or some other data warehouse and use its aggregates if needed. Although at the 100K scale you can use SQLite and it can handle that scale of data trivially on commodity laptops.

So your experience is interesting indeed.