> Excel added tables over a decade ago

I ran into this... last year when trying to do something with excel (I forget what excactly, apart from it needing joins and some analysis between several datasets).

It felt so unintuative with the tables "embedded" into sheets, it feels like they should be a sheet or a table, not both.

Power queries seemed a really neat tool for non-coders to munge data as needed.

Not sure what you mean by "power queries", but Google Sheets support SQL queries.

Would be easier to see on an example.

PowerQuery. It's a tool built into Excel. It's a GUI that wraps an almost purely-functional DSL designed for ETL and data munging, called the M language. You can either use the GUI or write the code directly. It has first class functions and closures and normies are programming in it. It's great. More people should know about it.

Btw it's kind of funny seeing so many HN users, many of whom must be working on software that competes with Excel either directly or indirectly, who are so unknowing of the full capabilities of Excel, capabilities that are the bread and butter of any e.g. financial analyst, or logistics manager, or any smart non-programmer white collar worker. Maybe this "hacker repulsion field" is the secret of its dominance -- you can't compete with it if you never learn what it can do.

Your aside is exactly why I wanted to use a spreadsheet. It's the only tool that has that market penetration for non-programmers, and I wanted to see what made it tick. It seems like that may have backfired by not using Excel, however.

If you enable PowerPivot (hidden in the COM addons settings) you'll get a quite capable data analysis tool for small-ish data with M + DAX with some rough edges.

They're the only reason I actually like to use Excel now. PowerBI has those natively built-in in a more modern iteration but is not as flexible (little direct data entry capability). That said PBI is ultimately meant for reporting.

I would love to understand more what limitations you faced with GSheets that are non-existent in Excel. I used to think the same but then I built some pretty complex computations in GSheets that I could not easily replicate in Excel. And U have built some complex stuff in both.

Extending on this, if you want to make really powerful business software — ingest and emit Excel sheets that other departments can use in their own flows.

PowerQuery is powerful, but also compromised by the fact it is so closely tied to Excel. If you want to do data transformation tasks (such as joins) on your Excel data and you don't want to learn R or Python+Pandas then you might be better off doing it in a no-code tool designed explicitly for the task, for example Alteryx (if you have deep pockets) or Easy Data Transform (if you don't).

There is another reason that is less often openly disclosed, but very much in play. I recently proposed RStudio or Python/Panda combo to deal with some of the limitations of Excel ( which also conveniently bypasses Alteryx's cost ), but the response I got was somewhat surprising, because it revolved around and I am paraphrasing 'will others be able to learn and use it'. And here is the rub. Excel has a lot of online tutorials and in some ways it is a known quantity. And, well, some people don't want to learn new thingsshrug.

No code data wrangling tools are significantly easier to learn than R or Python+Pandas. That is their main selling point (inevitably they trade some flexibility for this) and there is a range of them to cover all budgets.

In this context I’m interpreting DSL to mean “domain-specific language”

Querying Google sheets SQL doesn't support joins

> My point being, don't judge spreadsheets by Google Sheets.

I've used Google Sheets for years and greatly prefer it over Excel at this point. The killer feature is the sharing, which Excel does not do unless Microsoft 365 has greatly changed. The graphics, pivot tables, and functions are entirely sufficienty for cash flow and revenue models.

Seriously doubt Google Sheets lacks powerful features that Excel has (except niche features). Why would the developers intentionally cripple their product? It's not like any common operations on table data is too challenging for the Sheets team to implement. If there's any real difference between the softwares it's in their respective focus.

I really despise Microsoft (from an irrational place) and even I have to agree that Sheets is relatively crippled.

Even basic chart types are not supported, I think it might be due to limits of what's possible in the browser.

Granted though, excel can't backed on bigquery.

Google Sheets is nowhere close to Excel in terms of features, and not just niche ones. The biggest being tables, IMO.

Not intentionally cripple. It takes years to get to feature parity even with laser focus. If that focus isn't there (deciding Sheets has a slightly different positioning in the market for instance) it's not getting to feature parity.