Earlier quoted context omitted.
Specifically the "Lambda: The Ultimate" bit.
Which is one of my favourite PLT websites: http://lambda-the-ultimate.org
Where I heard about this development nearly three months ago:
Lambda: The Ultimate Excel Worksheet Function
91–100 of 112 posts
Re: Lambda: The Ultimate Excel Worksheet Function
#92The PhD work of Sruti Ragavan appears to lie behind part of this development. She hasn't defended her thesis yet, but she's been recruited by the MSR Cambridge team behind this work, where she interned in 2018, IIUC just before she began her PhD work.
Re: Lambda: The Ultimate Excel Worksheet Function
#93I don't understand the point of this function, when Excel already has Power Query. It doesn't seem like anyone who is literate in functional programming would want to use this, and anyone who isn't up to it wouldn't either. One of the most annoying things about Excel is it has so many parts apparently designed by people or groups that didn't talk to each other and didn't have a grasp of all the rest of it, let alone…
This is going to make writing spreadsheets to do basic engineering calculations far more clear.
Re: Lambda: The Ultimate Excel Worksheet Function
#94Earlier quoted context omitted.
I have a BS in Computer Science and whenever I help people pick up programming I notice scope is always one of the toughest concepts for them to grasp. It could of course be a reflection of my teaching ability, but it always seems to be a tough one.
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 to grasp it: especially when dealing with abstract concepts such as scopes.
Re: Lambda: The Ultimate Excel Worksheet Function
#95Earlier quoted context omitted.
> I don't understand the point of this function, when Excel already has Power Query. Because Power Query is not a spreadsheet application, and has some much more severe performance cliffs than Excel proper does.
To you and easton, my point is that even if Power Query has shortcomings, it's clearly the best thing to build on and improve, assuming VBA is dying a slow death and can't be revived. Even if, like, you wanted to make another separate language, it should still resemble Power Query, only better. I don't think people at Microsoft are looking at Excel as a whole, like lost souls squatting in a mansion and building sand…
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.
Accumulation patterns perform abysmally even with data in the 100Ks of elements. Say you have a table of inventory movements and want instead a snapshot table of inventory at point in time. You can do an O(n^2) self-join of a table with itself to all records with a lesser date, summing all movements to derive a total quantity at that time.
If you want to use an accumulation pattern, you can sort and cast your table to a list of records and then use List.Accumulate to iterate over each list element, deriving a new field with the running total of inventory amount. If you do this, you will find that it falls right over even with 1Ks or 10Ks of records. This is because the intermediate list that you're appending to through the accumulation is itself a lazy stream. Thus, you have to use List.Buffer at each step. Even with List.Buffer at each step, this solution falls over at high 10Ks or low 100Ks of records.
Incredibly unintuitively, you can use List.Generate with an already-buffered input list to derive a new list that can then be cast back to a table, though this still struggles with 100Ks of records.
If your snapshots can be aggregates, then you can happily throw out the idea of such an accumulation pattern and just join to a date table at the appropriate grain with all movement records less than or equal to the date in that date table.
I'll note that I regularly speak with several of the people whose blogs you will inevitably come across when performance tuning Power Query. The approaches above are the current state of the art in PQ for iteration and accumulation patterns. This is not an appeal to authority or a brag. This is to highlight the difference with the Excel spreadsheet formula approach below, which even beginners can derive from first principals.
In an Excel spreadsheet, for the same challenge, you just define a new column with a special first row formula, and each subsequent cell referencing the row above. This will happily run right up to the spreadsheet row limit with no performance concerns. If you really want, you can spill over to multiple spreadsheets, which is clunky to manage, but still performs just fine, and degrades slowly. The M approaches above hit a cliff and start hanging.
Excel formulas make it trivial to reference arbitrary cells. M is a nearly-general purpose language. PQ uses M, but as a framework for writing M, it has a strong emphasis on a query/table paradigm. A table-based operation model cuts against the grain of a spreadsheet, because a spreadsheet is a collection of arbitrary cells. A tabular approach is a collection of similarly shaped records stacked one upon the other. These two paradigms have a fair amount of overlap, but are not isomorphic. There are things trivial to express in one that become difficult bordering on impossible in the other.
Re: Lambda: The Ultimate Excel Worksheet Function
#96Funny how the top of the article starts with "custom functions without code" and then immediately shows code. I get that calling code by its name can make it sound scary, but this whole notion of it being 'easy because it is not code' seems to be a big fat lie for comfort. Same goes for the magic no-code systems where code is replaced with 'expressions' or graphical 'workflows' which essentially is exactly the same t…
While formulas are definitely code in a general sense, 'code' the Excel-verse generally refers to VBA code embedded in the workbook.
VBA's potential as an attack vector results in it being unavailable or heavily restricted in many corporate environments through stuff like Group Policies[1]. And I've worked with some clients whose IT goes a step further and completely blocks sending or receiving emails with .xlsm[2] attachments.
Since lambda-defined logic is all formula-based, it's not considered 'code' in that sense and can be used and passed around as a standard Excel file without any of the VBA-oriented restrictions. So you can approach your Excel workbook more like a programming project, centrally defining your complex logic once and referencing it elsewhere every time you want to use it. This is super helpful for audibility and maintenance, while staying within the bounds of what'll be applicable/usable across any Excel environment.
[1] https://4sysops.com/archives/restricting-or-blocking-office-...
[2] .xlsm is the extension Excel uses for spreadsheets containing VBA code
Re: Lambda: The Ultimate Excel Worksheet Function
#97Earlier quoted context omitted.
To you and easton, my point is that even if Power Query has shortcomings, it's clearly the best thing to build on and improve, assuming VBA is dying a slow death and can't be revived. Even if, like, you wanted to make another separate language, it should still resemble Power Query, only better. I don't think people at Microsoft are looking at Excel as a whole, like lost souls squatting in a mansion and building sand…
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 well in PowerQuery. I would have thought that 100K would be peanuts for it.
Mine is a SaaS however, so the user’s laptop isn’t a constraint, and I can transparently throw a million records in BigQuery or some other data warehouse and use its aggregates if needed. Although at the 100K scale you can use SQLite and it can handle that scale of data trivially on commodity laptops.
So your experience is interesting indeed.
Re: Lambda: The Ultimate Excel Worksheet Function
#98Earlier quoted context omitted.
To you and easton, my point is that even if Power Query has shortcomings, it's clearly the best thing to build on and improve, assuming VBA is dying a slow death and can't be revived. Even if, like, you wanted to make another separate language, it should still resemble Power Query, only better. I don't think people at Microsoft are looking at Excel as a whole, like lost souls squatting in a mansion and building sand…
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, where you believe Power Query inadequate, and at the same time have an alternate solution to provide a benchmark of what is adequate.
Re: Lambda: The Ultimate Excel Worksheet Function
#99Earlier 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…
I'm skeptical. I want an example, because my experience differs.
Re: Lambda: The Ultimate Excel Worksheet Function
#100Earlier quoted context omitted.
To you and easton, my point is that even if Power Query has shortcomings, it's clearly the best thing to build on and improve, assuming VBA is dying a slow death and can't be revived. Even if, like, you wanted to make another separate language, it should still resemble Power Query, only better. I don't think people at Microsoft are looking at Excel as a whole, like lost souls squatting in a mansion and building sand…
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"?