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

Lambda: The Ultimate Excel Worksheet Function

microsoft.com

Post reply on HN
Print view View original

101–110 of 112 posts

Re: Lambda: The Ultimate Excel Worksheet Function

#101

Post by Closi »

perl4ever wrote: post #100
Closi wrote: post #71

Earlier quoted context omitted.

PowerQuery isn’t a replacement for excel though - it’s a data preprocessing tool for analysts. It’s not going to replace functionality of core spreadsheet-based excel for accountants, for instance, who typically won’t have a use for PowerQuery as their data is structured differently.

I am not an accountant per se, but I work for an accounting organization. Could you give an example of what you mean by "structured differently"?

PowerQuery assumes tabular data where each column is the data type and each row is a data element / entity. It is structured similar to a database.

In a spreadsheet the data is much less structured which is where a lot of the power comes from - for instance PowerQuery doesn’t really support things like subtotals easily, or doing scratch-calculations, or building quick financial models. It is closer to a paper-ledger with calculations scribbled into the margins than a big-data database.

PowerQuery is more about ingesting lots of data and cleaning it, while finance is often about working stuff out and playing with numbers to see what happens - and playing with numbers is easier in a less-structured-loosely-typed environments.

Re: Lambda: The Ultimate Excel Worksheet Function

#102

Post by Dangeranger »

bickeringyokel wrote: post #74
Dangeranger wrote: post #54

Earlier quoted context omitted.

Maybe, there are hypothesis’ that the Universe is infinite. The observable Universe is finite.

If the universe is infinite, Then the observable universe is only as finite as the length of your life and capability to traverse through space.

Not necessarily. The observable Universe could also be reducing over time. Distant galaxies are accelerating in their travel away from our observation position because space itself is expanding. This means that over long periods of time, objects at the periphery of the observable Universe will red shift out of view.

Re: Lambda: The Ultimate Excel Worksheet Function

#103

Post by oooooooooooow »

VVertigo wrote: post #94
oooooooooooow wrote: post #51

Earlier quoted context omitted.

Strange! Sadly I don't remember the experience of learning about scope myself (I was too young), now I find it hard to see the "mind state" that makes it hard to understand. Isn't it a feature of natural languages to have the same word assume different meanings depending on where it's used? The concept translates nicely, and in PLs it's completely explicit whenever this happens.

That is an interesting thought: although, it is something that non-native speakers struggle with when learning a new language. I wonder if that is a factor when learning a new computer language/concept too. I suspect it is also related to the Curse of Knowledge ( https://en.wikipedia.org/wiki/Curse_of_knowledge ). Once you are past the hurdle of initially learning a concept it makes it hard to imagine not being able…

Exactly what I was trying to express (the Curse of knowledge), thank you for bringing it up, wasn't aware of the phrase.

Re: Lambda: The Ultimate Excel Worksheet Function

#104

Post by jhgb »

msla wrote: post #7

The "Lambda" paper Steele and Sussman never wrote! http://lambda-the-ultimate.org/papers Including: "Lambda the Ultimate Imperative" "Lambda the Ultimate Declarative"

Doesn't Sussmann's propagator model work kind of count as "Lambda the Ultimate Spreadsheet"?

Re: Lambda: The Ultimate Excel Worksheet Function

#105

Post by greggyb »

perl4ever wrote: post #98
greggyb wrote: post #95

Earlier quoted context omitted.

Power Query's streaming semantics for tables and lists can lead to severe performance issues with even modest data volumes. Table.Buffer and List.Buffer offer some small amount of control, but it's likely that you have a pipeline that creates a series of intermediate table and/or list values. Every single table and list function (with the exception of the buffer functions mentioned above) creates a new lazy stream. A…

Power Query falls over with thousands of items? That's not my experience. At work, the data usually isn't very large, but I have experimented on my own time with, for instance, a public covid data file that I think was several GB. I also thought lazy semantics is a good thing, not a fundamental flaw. Rather than debate, I would be interested enough to spend some time on a sample problem, if you could provide one, whe…

Not "PQ falls over with 1Ks of items," but rather "the M language does not do well with accumulation patterns on tables; naive approaches can hit significant performance issues in the 1Ks of records and sophisticated approaches struggle with 100Ks."

These are two very different statements. I've happily used PQ to ingest GBs of data. Its streaming semantics are fine to great for some types of processing and introduce performance cliffs for others. There's no binary judgment to be made here. Laziness is neither a fundamental flaw not an unmitigated good.

I've already shared one specific pattern above. I can share some mocked up data if you need me to, but that might be a day or two. Also, feel free to reach out via email (in my profile).

Re: Lambda: The Ultimate Excel Worksheet Function

#106

Post by greggyb »

louisvgchi wrote: post #97
greggyb wrote: post #95

Earlier quoted context omitted.

Power Query's streaming semantics for tables and lists can lead to severe performance issues with even modest data volumes. Table.Buffer and List.Buffer offer some small amount of control, but it's likely that you have a pipeline that creates a series of intermediate table and/or list values. Every single table and list function (with the exception of the buffer functions mentioned above) creates a new lazy stream. A…

As someone developing essentially a competitor to Excel-and-PowerQuery/M, I find all this very interesting. My language is strict and statically typed. However, after arrays (tables are arrays of records conceptually) exceed a certain length, rather than processing them in-memory as arrays, they will be offloaded to storage and processed (transparently) in a streaming fashion. I’m surprised that this doesn’t work wel…

Feel free to reach out via email if you want to follow up. My address is in my profile.

I'll note, as I did to a sibling reply of yours, I made observations about a specific pattern that showcases performance issues in PQ/M. PQ/M easily scales beyond 100Ks of records, but not for arbitrary processing patterns.

Re: Lambda: The Ultimate Excel Worksheet Function

#107

Post by perl4ever »

Closi wrote: post #101
perl4ever wrote: post #100

Earlier quoted context omitted.

I am not an accountant per se, but I work for an accounting organization. Could you give an example of what you mean by "structured differently"?

PowerQuery assumes tabular data where each column is the data type and each row is a data element / entity. It is structured similar to a database. In a spreadsheet the data is much less structured which is where a lot of the power comes from - for instance PowerQuery doesn’t really support things like subtotals easily, or doing scratch-calculations, or building quick financial models. It is closer to a paper-ledger…

>PowerQuery doesn’t really support things like subtotals easily,

Subtotals? I was used to using GROUPING SETS with Oracle SQL, and found I could roll my own in Power Query. It's a good example of exactly why I like it.

Also, Power Query doesn't prevent you from using the regular table total feature or a pivot table based off of the Power Query output.

That is, even if Power Query doesn't provide all the subtotaling features you'd like in the way you'd like, it doesn't restrict you from anything, does it?

> or doing scratch-calculations, or building quick financial models

I do use it to do all sorts of ad hoc calculations - for instance, it can ingest PDF files or HTML with tables.

It sounds as if you're saying it's too complicated for really trivial calculations?

Re: Lambda: The Ultimate Excel Worksheet Function

#108

Post by perl4ever »

greggyb wrote: post #105
perl4ever wrote: post #98

Earlier quoted context omitted.

Power Query falls over with thousands of items? That's not my experience. At work, the data usually isn't very large, but I have experimented on my own time with, for instance, a public covid data file that I think was several GB. I also thought lazy semantics is a good thing, not a fundamental flaw. Rather than debate, I would be interested enough to spend some time on a sample problem, if you could provide one, whe…

Not "PQ falls over with 1Ks of items," but rather "the M language does not do well with accumulation patterns on tables; naive approaches can hit significant performance issues in the 1Ks of records and sophisticated approaches struggle with 100Ks." These are two very different statements. I've happily used PQ to ingest GBs of data. Its streaming semantics are fine to great for some types of processing and introduce…

>I've already shared one specific pattern above

If you mean this:

"Say you have a table of inventory movements and want instead a snapshot table of inventory at point in time"

Then I can make my own data to play with - I only want to be clear about the constraints. Would 500K records be enough to obviate the distinction between naive and non-naive approaches? Can you quantify (not precisely) "struggle"?

I have used Table.Buffer, but I probably don't thoroughly understand its use yet.

(I belatedly realized your problem is something I've done with Sharepoint list history recently, but not that many records, so I'm going to look for a public dataset to try)

P.P.S. I guess it also makes me think - I frequently am getting my data from an Oracle database, so if something is easier done there, I'd put it in the SQL. Analytic functions are convenient.

P.P.P.S. Aha! I found a file of parking meter transactions for 2020 in San Diego, which is about 140MB and almost 2 million records. This seems like a good test because not only is it well over the number you said was problematic, but it's well over the number of rows you can have directly in one Excel sheet.

https://data.sandiego.gov/datasets/parking-meters-transactio...

Re: Lambda: The Ultimate Excel Worksheet Function

#109

Post by perl4ever »

greggyb wrote: post #105
perl4ever wrote: post #98

Earlier quoted context omitted.

Power Query falls over with thousands of items? That's not my experience. At work, the data usually isn't very large, but I have experimented on my own time with, for instance, a public covid data file that I think was several GB. I also thought lazy semantics is a good thing, not a fundamental flaw. Rather than debate, I would be interested enough to spend some time on a sample problem, if you could provide one, whe…

Not "PQ falls over with 1Ks of items," but rather "the M language does not do well with accumulation patterns on tables; naive approaches can hit significant performance issues in the 1Ks of records and sophisticated approaches struggle with 100Ks." These are two very different statements. I've happily used PQ to ingest GBs of data. Its streaming semantics are fine to great for some types of processing and introduce…

Ok, I agree that PQ is slow. It is possible to calculate a running total of a column in a million row table before the sun burns out though.

I am very not an algorithm person, but I got a huge speedup from a "parallel prefix sum" instead of the obvious sequential approach or the even worse N^2.

I translated this to M by rote and trial and error (page 2): https://www.cs.utexas.edu/~plaxton/c/337/05f/slides/Parallel...

Implementing the parallel, recursive solution got me a million rows in about three and a half minutes.

Fill down (which I had to do anyway to compare) was about 10 seconds.

So...probably not the first choice in this scenario but could be worse?

Re: Lambda: The Ultimate Excel Worksheet Function

#110

Post by Closi »

perl4ever wrote: post #107
Closi wrote: post #101

Earlier quoted context omitted.

PowerQuery assumes tabular data where each column is the data type and each row is a data element / entity. It is structured similar to a database. In a spreadsheet the data is much less structured which is where a lot of the power comes from - for instance PowerQuery doesn’t really support things like subtotals easily, or doing scratch-calculations, or building quick financial models. It is closer to a paper-ledger…

>PowerQuery doesn’t really support things like subtotals easily, Subtotals? I was used to using GROUPING SETS with Oracle SQL, and found I could roll my own in Power Query. It's a good example of exactly why I like it. Also, Power Query doesn't prevent you from using the regular table total feature or a pivot table based off of the Power Query output. That is, even if Power Query doesn't provide all the subtotaling f…

> It sounds as if you're saying it's too complicated for really trivial calculations?

I'm saying it's not the right tool for some classes of calculations.

For instance I work in designing warehouses, and use both tools. Here are some use cases where Excel doesn't do well and I would use PowerQuery:

* Ingesting millions of historical orders

* Handling relational data

* Data cleaning and aggregations

Here are some example use cases where PowerQuery doesn't work as well, but Excel is perfectly good:

* What height should the pallet racking bays be in this warehouse, and how many pallets am I likely to fit in the building envelope? (considering my other space requirements)

* What's the likely transport impact of opening a new distribution point?

* Running lots of scenarios or sensitivities.

Why are these better in excel? Well there are just some things PowerQuery doesn't do well, for instance excel can take into account any other arbitrary cells value into it's own calculation, while in PowerQuery you generally have to use an intermediary table and joins to handle this.

Can both tools physically do it? Yes, it's just some problems suit one rather than the other, and identifying the right tool for the right problem saves you lots of time. One thing that makes Excel better for scratch calculations for example is the fact that it's a live environment (with PowerQuery you have to run it after changes to get the results back, and this can be really slow compared to excel).

Post reply on HN