Forum Discussion
Solved
Fill BLANKs down, up or both in column(s) - similar to Power Query
Hi Looked at +/- replicating Fill Down and Fill Up functionnalities in Power Query with a LAMBDA. My attempt below: // FillDownUp:
=LAMBDA(array,
LET(
Data, DRO...
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) ))
Sadly, I didn't see this question first time around!
My first thought is that the problem as posed doesn't really require the equivalent of the PQ FillUp functionality. It is really just FillDown but with non-standard initialisation using the first non-blank value.
FILLDOWNλ
= LET(
init, TAKE(TOCOL(column, 3), 1),
SCAN(init, column, LAMBDA(a, v, IF(v <> "", v, a)))
)Having defined a function that works with a single column one would naturally expect
= BYCOL(Table, FILLDOWNλ)to provide the solution for the complete table. Unfortunately, because of the Microsoft error in specifying the functionality required to handle arrays of arrays, one is stuck with implementing one's own version of BYCOL.
BYCOLλ
= LET(
n₀, COLUMNS(array),
n₁, QUOTIENT(n₀, 2),
n₂, n₀ - n₁,
A₁, TAKE(array, , n₁),
A₂, TAKE(array, , -n₂),
X₁, IF(n₁ > 1, BYCOLλ(A₁, FNλ), FNλ(A₁)),
X₂, IF(n₂ > 1, BYCOLλ(A₂, FNλ), FNλ(A₂)),
HSTACK(X₁, X₂)
)The double recursion is a bit painful but at least the function can be reused and it is straightforward to apply
= BYCOLλ(Table, FILLDOWNλ)
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.
