Hacker News Reader

Comment by tomnipotent | Hacker News Reader

> good way

But it is an accessible way. Your average Excel user can perform any number of useful transformations with simple formulas, and then throw that into a pivot table for useful aggregations (which is also a transformation).

> tables are an afterthought to spreadsheets

Excel power users have been solving this with VLOOKUPs for decades.

> therefore even trivial joins are always a pain in Excel

Trivial to a trained user. Excel has more advanced data modeling capabilities, which includes implicit joins when creating PivotTables from tables with relationships.

https://support.microsoft.com/en-us/office/create-a-data-mod...

View on HN

Lookups in excel are fine, at least now we have XLOOKUP but there's something depressing about an excel model which contains multiple massive grids of lookups. I find such things are much easier in a real join, where you can get your data into long format then write a single line of code to manipulate meaningful named columns.

The link leads to an article on PowerQuery which is a Power BI product. PowerQuery can surely do joins, but it doesn't operate within Excel's data model - cells and sheets. It's an external tool from the Power BI family from which integrates with Excel to a certain extent.

In a similar fashion you could've posted a link to running an SQL join query from Excel (which is surely doable for a trained user). Technically it's possible, but it would be wrong to pretend like the join is done in Excel.

> but it doesn't operate within Excel's data model

Power Query is an Excel add-on, and has been around much longer than Power BI and has nothing to do with it. This has been possible for almost a decade, since Excel integrated a robust in-memory columnar database capable of dealing with millions of rows.

> it would be wrong to pretend like the join is done in Excel

The join is done in Excel, I don't need to pretend. That you think Excel is just a bunch of sheets and cells is your own mistaken mental model of what the program is and can do.

Here's another reference:

"Now that Excel has a built-in Data Model"

https://support.microsoft.com/en-us/office/create-a-relation...

Can you point me to an article that shows a simple way how to do a simple SQL-like join (no aggregation) of two regions in the same spreadsheet? Two source tables, and one result table - all in the same spreadsheet.

Something that is as simple as this: https://www.youtube.com/watch?v=RYCtoRTEk84

I sent you two links with information, including images that show the process.

Here's a video of the process:

https://support.microsoft.com/en-us/office/merge-queries-and...

Here's another video:

https://www.youtube.com/watch?v=BV3srtI20Bo

Thank you. I didn't know this. I wouldn't call the process easy, but it's relatively straightforward.

Reply on news.ycombinator.com