3 ms·

> It also really annoys me when I see Linux/FOSS partisans tell Windows normies "oh you can do everything you can do in Excel in LibreOffice Calc" -- no you fuc

by taviso 4y ago

> It also really annoys me when I see Linux/FOSS partisans tell Windows normies "oh you can do everything you can do in Excel in LibreOffice Calc" -- no you fucking well cannot. (And I use Linux on my personal computers full time). It seems like your argument is that alternatives don't have PowerQuery. That might be true (I don't even know what it is), but isn't that like saying Linux can't compete with Windows, because it doesn't have Internet Explorer? I mean, it doesn't, but there are excellent alternatives that can accomplish exactly the same task. Unless you can come up with an example task that can't be completed, then it seems like it's just a matter of opinion which is the better solution. As far as I know both Google Sheets and LibreOffice have SQL and Pivot Tables, and -- believe it or not -- Lotus 1-2-3 had "/Data queries" in 1989. Naturally, the queries possible in 1-2-3 were limited, but you really could query large tables for things like "[Date] <= #date(2017,6,1)", which is the first result I got from typing "Power Query example statement" into Google.

briandear 4y ago
Do Sheets and LibreOffice have Solver? No. They don’t.
- deknos 4y ago
  LibreOffice does have an solver. but i have no idea how it compares to others.
- taviso 4y ago
  Lotus 1-2-3 had a Solver (two actually, one called "Solver" and one called "What-If") in 1989. Yes, LibreOffice has one too. Does Microsoft tell their customers they invented these features? Just a few days ago I saw a Windows developer who thought Microsoft invented conditional breakpoints.
_dain_ 4y ago
>It seems like your argument is that alternatives don't have PowerQuery. That might be true (I don't even know what it is), but isn't that like saying Linux can't compete with Windows, because it doesn't have Internet Explorer? I mean, it doesn't, but there are excellent alternatives that can accomplish exactly the same task. It's true that Google sheets and LibreOffice don't have Powerquery, and that's a big pain. But the worse thing is that they don't have tables. As in, the "format as table" button in Excel. As in, the bread and butter of anyone who gets serious work done in Excel. Maybe it's a problem of naming -- "format" makes people think it's just about aesthetics, but actually it imparts real semantic structure onto a rectangular grid of data. It also isn't the same thing as pivot tables, with which they are often confused. It gives the grid a name that you can refer to in formulae, and the columns are named too, with their names living inside the table namespace ("structured references" is what Microsoft calls it). The table automatically expands its boundaries when you start typing a column header to the right of the current columns, and likewise it expands to comprise the row beneath it if you type values into that row. And it has smart indexing: there's special syntax to refer to "this table" and "this row" in formulae. So you can have say, a table named "ExpensesTable", labelled "Date", "Type of Expense" and "Amount" in columns A:C. Then you can type "Tax" at the top of column D, it will expand the table to include a new blank column for Tax. Then in D2, type =[@[Amount]] * 0.2 and it will automatically fill down the Tax column with 20% of the value of the Amount columns. Then in a cell outside the table, do =sum(ExpensesTable[Amount]) to get the total amount of expenses. These are both simple examples; you can do more complex and interesting things involving multiple columns, ranges of columns, joins, etc. The point is the semantic structure that makes your spreadsheet more than just a rectangular soup of cells, so you don't have to claw through endless cryptic "G70:$K100" cell references. If we add a new row or column, we don't have to alter any formulae at all; the bounds are automatically resized on the cell arrays that the column names refer to. Think of it like a mutable resizable dataframe. It's the core data structure of an efficient, scalable, maintainable Excel document. More about structured references: https://support.microsoft.com/en-us/office/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e https://support.microsoft.com/en-us/office/using-structured-... Also the "You Suck At Excel" talk by Joel Spolsky: https://www.youtube.com/watch?v=0nbkaYsR94c https://www.youtube.com/watch?v=0nbkaYsR94c And no, I have no idea why the eggheads at Google don't implement this for Sheets. Maybe Microsoft has a patent on it? Wouldn't surprise me. But this is why you'll have to pry Excel out of spreadsheet jockeys' cold dead hands -- the alternatives don't have this basic thing.
- taviso 4y ago
  You need to come up with a task that cannot be accomplished, remember you said "no you fucking well cannot [do everything]", but so far I've seen no examples. I mean, isn't this just a button that adds some named ranges for you? You can replicate the exact example you gave with named ranges. If there is something it can do that named ranges can't, then please use that example instead. Similarly, if you think there is something that "Power Query" can do that SQL cannot, then please show that. I literally use Lotus 1-2-3 for UNIX (I'm not kidding! http://123r3.net http://123r3.net). So far, all of the examples I've seen you give could have been done in 1989 on a VT100 terminal connected to SystemV. You could even write a quick macro in that generates the named ranges from column headers with one keystroke, it would be really trivial.
  - deleted 4y ago
    [deleted]
  - _dain_ 4y ago
    >I mean, isn't this just a button that adds some named ranges for you? No, named ranges don't automatically expand when you add new rows, and they aren't automatically created when you add new columns. And they don't remain in groups, e.g. you can't make a reference like Namedrange1:Namedrange3, but in a table you can do Column1:Column3. Named ranges exist in a global namespace; column references exist in a per-table namespace. The table syntax makes columnwise operations clearer to express in formulae. Let's say you want to refer to the cell in the same row as the current cell, but in a different column: how do you do that if everything is just a named range? You need to do some kind of juggling with indexing and lookups, or else fall back to alphabet soup A1/R1C1 style referencing, because a named range is only good if you want to do an operation on every cell in the range. But that's often not what you want! In tables it's as simple as [@[other column]]. You would know this if you actually read the documentation or watched the video I posted. Or I could just repeat myself again (maybe I will write a macro to automate such tedium). >Similarly, if you think there is something that "Power Query" can do that SQL cannot, then please show that. Grab data from a csv file, a JSON file, a SQL database, and an Excel sheet, and combine them all together using a normie-friendly GUI. Your question doesn't even make sense, it's like a type error. SQL and PowerQuery are not competing technologies, they're complementary. >You could even write a quick macro in that generates the named ranges from column headers with one keystroke, it would be really trivial. Yeah and you can also make Dropbox by getting an FTP account, mounting it locally with curlftpfs, and then using SVN or CVS on the mounted filesystem. Spreadsheets are the only remaining programming system that people not inducted into the Programming Cult use.

by taviso 4y ago

briandear 4y ago

Do Sheets and LibreOffice have Solver? No. They don’t.

deknos 4y ago
LibreOffice does have an solver. but i have no idea how it compares to others.
taviso 4y ago
Lotus 1-2-3 had a Solver (two actually, one called "Solver" and one called "What-If") in 1989. Yes, LibreOffice has one too. Does Microsoft tell their customers they invented these features? Just a few days ago I saw a Windows developer who thought Microsoft invented conditional breakpoints.

_dain_ 4y ago

>It seems like your argument is that alternatives don't have PowerQuery. That might be true (I don't even know what it is), but isn't that like saying Linux can't compete with Windows, because it doesn't have Internet Explorer? I mean, it doesn't, but there are excellent alternatives that can accomplish exactly the same task. It's true that Google sheets and LibreOffice don't have Powerquery, and that's a big pain. But the worse thing is that they don't have tables. As in, the "format as table" button in Excel. As in, the bread and butter of anyone who gets serious work done in Excel. Maybe it's a problem of naming -- "format" makes people think it's just about aesthetics, but actually it imparts real semantic structure onto a rectangular grid of data. It also isn't the same thing as pivot tables, with which they are often confused. It gives the grid a name that you can refer to in formulae, and the columns are named too, with their names living inside the table namespace ("structured references" is what Microsoft calls it). The table automatically expands its boundaries when you start typing a column header to the right of the current columns, and likewise it expands to comprise the row beneath it if you type values into that row. And it has smart indexing: there's special syntax to refer to "this table" and "this row" in formulae. So you can have say, a table named "ExpensesTable", labelled "Date", "Type of Expense" and "Amount" in columns A:C. Then you can type "Tax" at the top of column D, it will expand the table to include a new blank column for Tax. Then in D2, type =[@[Amount]] * 0.2 and it will automatically fill down the Tax column with 20% of the value of the Amount columns. Then in a cell outside the table, do =sum(ExpensesTable[Amount]) to get the total amount of expenses. These are both simple examples; you can do more complex and interesting things involving multiple columns, ranges of columns, joins, etc. The point is the semantic structure that makes your spreadsheet more than just a rectangular soup of cells, so you don't have to claw through endless cryptic "G70:$K100" cell references. If we add a new row or column, we don't have to alter any formulae at all; the bounds are automatically resized on the cell arrays that the column names refer to. Think of it like a mutable resizable dataframe. It's the core data structure of an efficient, scalable, maintainable Excel document. More about structured references: https://support.microsoft.com/en-us/office/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e https://support.microsoft.com/en-us/office/using-structured-... Also the "You Suck At Excel" talk by Joel Spolsky: https://www.youtube.com/watch?v=0nbkaYsR94c https://www.youtube.com/watch?v=0nbkaYsR94c And no, I have no idea why the eggheads at Google don't implement this for Sheets. Maybe Microsoft has a patent on it? Wouldn't surprise me. But this is why you'll have to pry Excel out of spreadsheet jockeys' cold dead hands -- the alternatives don't have this basic thing.

taviso 4y ago
You need to come up with a task that cannot be accomplished, remember you said "no you fucking well cannot [do everything]", but so far I've seen no examples. I mean, isn't this just a button that adds some named ranges for you? You can replicate the exact example you gave with named ranges. If there is something it can do that named ranges can't, then please use that example instead. Similarly, if you think there is something that "Power Query" can do that SQL cannot, then please show that. I literally use Lotus 1-2-3 for UNIX (I'm not kidding! http://123r3.net http://123r3.net). So far, all of the examples I've seen you give could have been done in 1989 on a VT100 terminal connected to SystemV. You could even write a quick macro in that generates the named ranges from column headers with one keystroke, it would be really trivial.
- deleted 4y ago
  [deleted]
- _dain_ 4y ago
  >I mean, isn't this just a button that adds some named ranges for you? No, named ranges don't automatically expand when you add new rows, and they aren't automatically created when you add new columns. And they don't remain in groups, e.g. you can't make a reference like Namedrange1:Namedrange3, but in a table you can do Column1:Column3. Named ranges exist in a global namespace; column references exist in a per-table namespace. The table syntax makes columnwise operations clearer to express in formulae. Let's say you want to refer to the cell in the same row as the current cell, but in a different column: how do you do that if everything is just a named range? You need to do some kind of juggling with indexing and lookups, or else fall back to alphabet soup A1/R1C1 style referencing, because a named range is only good if you want to do an operation on every cell in the range. But that's often not what you want! In tables it's as simple as [@[other column]]. You would know this if you actually read the documentation or watched the video I posted. Or I could just repeat myself again (maybe I will write a macro to automate such tedium). >Similarly, if you think there is something that "Power Query" can do that SQL cannot, then please show that. Grab data from a csv file, a JSON file, a SQL database, and an Excel sheet, and combine them all together using a normie-friendly GUI. Your question doesn't even make sense, it's like a type error. SQL and PowerQuery are not competing technologies, they're complementary. >You could even write a quick macro in that generates the named ranges from column headers with one keystroke, it would be really trivial. Yeah and you can also make Dropbox by getting an FTP account, mounting it locally with curlftpfs, and then using SVN or CVS on the mounted filesystem. Spreadsheets are the only remaining programming system that people not inducted into the Programming Cult use.