4 ms·

> 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

> 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-model-in-excel-87e7a54c-87dc-488e-9410-5c75dbcb0f7b https://support.microsoft.com/en-us/office/create-a-data-mod...

jsmith99 5y ago
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.
dgudkov 5y ago
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.
- tomnipotent 5y ago
  > 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-relationship-between-tables-in-excel-fe1b6be7-1d85-4add-a629-8a3848820be3 https://support.microsoft.com/en-us/office/create-a-relation...
  - dgudkov 5y ago
    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 https://www.youtube.com/watch?v=RYCtoRTEk84
    - tomnipotent 5y ago
      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-join-tables-cbd17828-7a50-4dc6-9aac-20af4ef6d8a6 https://support.microsoft.com/en-us/office/merge-queries-and... Here's another video: https://www.youtube.com/watch?v=BV3srtI20Bo https://www.youtube.com/watch?v=BV3srtI20Bo
      - dgudkov 5y ago
        Thank you. I didn't know this. I wouldn't call the process easy, but it's relatively straightforward.

jsmith99 5y ago

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.

dgudkov 5y ago

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.

tomnipotent 5y ago
> 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-relationship-between-tables-in-excel-fe1b6be7-1d85-4add-a629-8a3848820be3 https://support.microsoft.com/en-us/office/create-a-relation...
- dgudkov 5y ago
  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 https://www.youtube.com/watch?v=RYCtoRTEk84
  - tomnipotent 5y ago
    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-join-tables-cbd17828-7a50-4dc6-9aac-20af4ef6d8a6 https://support.microsoft.com/en-us/office/merge-queries-and... Here's another video: https://www.youtube.com/watch?v=BV3srtI20Bo https://www.youtube.com/watch?v=BV3srtI20Bo
    - dgudkov 5y ago
      Thank you. I didn't know this. I wouldn't call the process easy, but it's relatively straightforward.