Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Excel warriors who save governments and companies from spreadsheet errors

wired.co.uk

Post reply on HN
Print view View original

71–80 of 193 posts

Re: Excel warriors who save governments and companies from spreadsheet errors

#71

Post by jrumbut »

cosmie wrote: post #50
blendo wrote: post #40

Earlier quoted context omitted.

From the M-code link: “A core capability of Power Query is to filter and combine, that is, to mash-up data from one or more of a rich collection of supported data sources. Any such data mashup is expressed using the Power Query M Formula Language. It's a functional, case sensitive language similar to F#.” Does M-code serve as a replacement for (the abysmal) VBA? And how similar is it to F#?

> Does M-code serve as a replacement for (the abysmal) VBA? Sort of depends. M (and PowerQuery in general) is fundamentally about data processing. If you were only dropping into VBA for ETL purposes, then chances are you can use PowerQuery/M instead. With the advantage that it exists in a standard xlsx file, rather than the macro-enabled xlsm files that are more heavily restricted in many environments. It's not inten…

Thanks for this great explainer. I discovered PowerQuery almost be accident (trying to just do a simple join).

The capabilities were nice (and according to your posts more extensive than I know!) but the interface was so beyond counterintuitive, no discoverability, so unlike any of the variety of environments I've encountered in 10 years of programming, and the performance was painfully slow (compared to an RDBMS).

I have hopes for MS moving Excel to a less unique interface (I heard smooth scrolling is coming!) and I will definitely be reading your links.

Re: Excel warriors who save governments and companies from spreadsheet errors

#72

Post by 2fast4you »

Sharlin wrote: post #69
askvictor wrote: post #27

Earlier quoted context omitted.

Not just Excel; Google Sheets too. Our school system has student codes in the format ABC0001 where ABC is the first three letters of the surname, and the number is sequential. So a student with a surname Martin might get a code MAR0004. Which Sheets, in it's wisdom, decides to interpret as the date March 2004. Go figure.

Excel being the de facto standard, Sheets has to try to be compatible with it, quirks and all.

Speaking of quirks, Excel still incorrectly assumes the year 1900 is a leap year. Even Excel has to try to be compatible with Excel.

https://docs.microsoft.com/en-us/office/troubleshoot/excel/w...

Re: Excel warriors who save governments and companies from spreadsheet errors

#73

Post by ecpottinger »

vikramkr wrote: post #8
NoPicklez wrote: post #4

I work at a large firm which has shifted itself from using Excel, to significantly promoting data analytics tools such as Alteryx in place of Excel. One of the MAJOR improvements from using Alteryx is that it is easier to audit and review the process. With Excel you can be down the rabbit hole of data manipulation or formula driven outputs and realise you have made a mistake but cannot find where or how. Or cannot fi…

The problem is that everyone has and knows how to use excel. And enough people in finance and consulting see excel skills as a mark of status that there's going to be some resistance to change there. A new tool has to be equally universal, easy to use for basic purposes while having advanced capabilities, etc. There are courses that teach supply chain management with solver and the like - having people shift over fro…

Not only have I seen spreadsheet used for function they were never intended for like using it as a text notepad. The main problem since spreadsheets are easy to set up, people create them without asking even the most basic safety functions.

Testing the range on inputs or outputs is not done enough, but worse using the wrong functions can give a sheet that looks good but is very wrong.

Re: Excel warriors who save governments and companies from spreadsheet errors

#74

Post by tijsvd »

askvictor wrote: post #27
hermitcrab wrote: post #22

Earlier quoted context omitted.

One of the big issues with spreadsheets is the lack of visibility. It is very hard to tell what is going on in any non-trivial spreadsheet. That something that visual data flow tools such as Alteryx, Knime and (my own) Easy Data Transform try to address. The other problem is specific to Excel. It just loves to mangle your data! The problem is so bad that geneticists have renamed some genes to stop Excel reinterpretin…

Not just Excel; Google Sheets too. Our school system has student codes in the format ABC0001 where ABC is the first three letters of the surname, and the number is sequential. So a student with a surname Martin might get a code MAR0004. Which Sheets, in it's wisdom, decides to interpret as the date March 2004. Go figure.

The age-old workaround for this used to be to type an apostrophe in front of the value, which forces Excel to interpret as text. IIRC that used to work in Google Sheets as well. Doesn't that trick work anymore?

Re: Excel warriors who save governments and companies from spreadsheet errors

#75

Post by tuatoru »

crispyporkbites wrote: post #21

Excel is a triumph of software. Never before has there been a REPL so powerful, a programming language so widely used, an environment so simple even mere mortals can produce magic - without it what would we have? The power of software would remain closed behind the Ivory gates of the self-titled engineers with development cycles, sprints and countless hours to make a single formulae change. Fear the excel less world,…

> without it what would we have?

In an ideal world, we would have pgsql (the PostgreSQL front-end) and AWK, and people who realize that thinking about the model before you start playing with layouts and copy-pasting formulas is a good idea.

Edit: Excel is a Dunning-Kruger syndrome enabler. People think they can do data analysis because they can type "=sum(B1:B4)" into a spreadsheet.

Re: Excel warriors who save governments and companies from spreadsheet errors

#76

Post by cosmie »

jrumbut wrote: post #71
cosmie wrote: post #50

Earlier quoted context omitted.

> Does M-code serve as a replacement for (the abysmal) VBA? Sort of depends. M (and PowerQuery in general) is fundamentally about data processing. If you were only dropping into VBA for ETL purposes, then chances are you can use PowerQuery/M instead. With the advantage that it exists in a standard xlsx file, rather than the macro-enabled xlsm files that are more heavily restricted in many environments. It's not inten…

Thanks for this great explainer. I discovered PowerQuery almost be accident (trying to just do a simple join). The capabilities were nice (and according to your posts more extensive than I know!) but the interface was so beyond counterintuitive, no discoverability, so unlike any of the variety of environments I've encountered in 10 years of programming, and the performance was painfully slow (compared to an RDBMS). I…

Yea, discoverability is a pain. Although importing data from a CSV file has migrated from their obtuse "Text Import Wizard" to PowerQuery in the most recent versions of Excel, so more people will be stumbling on it.

And also yes, the Power Query Editor interface sucks. It's intended to be intuitive for Excel users, which inversely makes it a bit counterintuitive for someone coming from a dev background. The GUI functionality is also pretty limiting and only exposes a fraction of the overall capabilities. That said, there's an "Advanced Editor" button in the Ribbon that'll open up a modal that's far more familiar for a dev; shows you all of the code/Steps for the query all at once and has IntelliSense for code completion. Still crude as far as a dev environment goes, but far more productive than flailing around in the GUI the whole time.

Since you're coming from a dev background, I'd highly encourage that M primer[1] I referenced. It's a crash course on everything that's relevant to a developer (type system, error handling, patterns, antipatterns, custom functions, execution logic, etc). Between that and glancing over the standard library[2], you can avoid using the GUI entirely except for when it's convenient (such as scaffolding out some tedious stuff to then clean up in the Advanced Editor).

> and the performance was painfully slow (compared to an RDBMS).

Also of note is that Excel is only one of the data sources PowerQuery supports. There are a ton of others[3], including many RDBMSs natively supported and generic ODBC support if you have the appropriate driver installed.

[1] https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-p...

[2] https://docs.microsoft.com/en-us/powerquery-m/power-query-m-...

[3] https://docs.microsoft.com/en-us/power-query/connectors/

Re: Excel warriors who save governments and companies from spreadsheet errors

#78

Post by watwut »

askvictor wrote: post #27
hermitcrab wrote: post #22

Earlier quoted context omitted.

One of the big issues with spreadsheets is the lack of visibility. It is very hard to tell what is going on in any non-trivial spreadsheet. That something that visual data flow tools such as Alteryx, Knime and (my own) Easy Data Transform try to address. The other problem is specific to Excel. It just loves to mangle your data! The problem is so bad that geneticists have renamed some genes to stop Excel reinterpretin…

Not just Excel; Google Sheets too. Our school system has student codes in the format ABC0001 where ABC is the first three letters of the surname, and the number is sequential. So a student with a surname Martin might get a code MAR0004. Which Sheets, in it's wisdom, decides to interpret as the date March 2004. Go figure.

You can set the format of the cell.

Re: Excel warriors who save governments and companies from spreadsheet errors

#80

Post by OzCrimson »

Excel bashing is easy but, where's a truly comparable alternative?

- Alteryx at $5000/year/user?

- A database that requires specialized skill or going through a DBA for every little need?

- Salesforce? It's hell to configure if you don't have the skill.

- Sharepoint? Again. If you're a small business or nonprofit, you've gotta hire or train someone before this is a serious conversation.

I would in no way suggest that an enterprise can run on Excel with centralized data and multiple users. And that's where I believe a lot of criticism of Excel is unfair: the comparisons are not apples to apples.

And let's be honest. EVERYTHING comes with a level of human error.

- Enterprise level databases can get so big that the corrupt data in them can be impossible to cleanse.

- Reports made by SQL people are often flawed.

Over my 7 years in an international company I discovered report after report that didn't accurately match business rules or they were based on loads of incomplete records, duplicates, inconsistencies, incorrect math, etc.

How about fixing the reports? Often the decision was "no." Fixing the reports wasn't something a department head wanted to pay a consultant for.

Eventually, I had the DBA stop sending me reports. I started exporting the raw data into Excel and made my own reports.

- Brand new reports can be impossible to get if they don't justify the cost of a consultant to create the report. (Ah, but there's Excel.)

- Databases get hacked and they crash.

So. Where's the tool that's perfect, free and easy to use? It doesn't exist.

Post reply on HN