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) ))
More correct name will be Fill Down and Up (not fill up and down) Named it UpDown as in reality this is what my approach does. Though, this is quite confusing ==> Initial post revised
Thank you SergeiBaklan & Patrick2788
What I had in mind (wasn't sure that was realistic) was to provide a single LAMBDA (to allow copy/paste in Name Manager) for cases like (but not only):
where, for whatever reason Power Query cannot be used. Added a couple of optional parameters:
I don't post the formula as it's a monster now. It's available in the attached workbook - seems to work decently... I stop there but will wait a couple of days before closing this thread in case you have feedback/recommendation (I'm gonna change the title of the discussion accordingly)
I like this formula. Sure that could be other variants, as for everything in Excel (and not only), but the only reason to revise the formula could be the performance, if it works fine on hundreds thousands of rows. On the other hand such is not practical for the majority of Excel cases.
It works fine and do it function, thank you for sharing.
I'm usually concerned by perf. (and scalabily) and did check with a few thousands of rows, no problem from my perspective although there are probably faster approaches. TBH I'm not too concerned about this for this kind of transformation as, theoritically, this isn't somethig people have to do ten times a day
Didn't post it but also checked with 'large' tables. To give you a sense (on Core i7/8 GB/SSD):
Random table 300k rows * 7 cols to fill down then up- LAMBDA 12 secs
- PQ (firewall off) 6 secsSame random table 500k rows * 7 cols to fill down then up
- LAMBDA 22 secs
- PQ (firewall off) 9 secsInteresting, thank you. Yes, good enough for most practical cases.
