Forum Discussion
Fill BLANKs down, up or both in column(s) - similar to Power Query
My variant:
Filler =LAMBDA(table,LET( header, TAKE(table, 1), data, DROP(table, 1), counter, SEQUENCE(COLUMNS(data)), FillDown, LAMBDA(a, v, IF(v = "", a, v)), Complete, LAMBDA(vector, LET(ini, TAKE(TOCOL(vector, 1), 1), SCAN(ini, vector, FillDown))), Stack, DROP(REDUCE("", counter, LAMBDA(a, v, HSTACK(a, Complete(INDEX(data, , v))))), , 1), VSTACK(header, Stack) ))
Still need to fully understand how this works but this seems very straightfoward 馃檪
The core functionality of BYCOL位 is also intended to be widely deployable. The assumption is that some function FN位 needs to be applied to every column of an array (or range). Rather than rewriting the function FN位 to address multiple columns, BYCOL位 splits to problem into two parts and solves each half independently. Recursion means that eventually one reaches the point where the argument is a single column and FN位 may be applied directly to give a single result.
Whereas the built-in BYCOL function will only return the result if each application of the function returns a scalar value, BYCOL位 uses HSTACK to build an array of arrays.
A problem with the alternative approach of using REDUCE and HSTACK to build an array of array is that the early results may be copied 10,000s of times during the stacking steps. This gets to be expensive in terms of computing resource. The bisection approach limits the steps of copying any given result to form build bigger datasets to 14 at the most.
I hope that by describing the intent of the 'home-knitted' helper function, I will simplify life for anyone planning to reverse engineer it from the code.