Forum Discussion

Lorenzo's avatar
Lorenzo
Silver Contributor
Nov 27, 2023
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...
excel
Excel for web
Excel on Mac
office 365
  • Patrick2788's avatar
    Patrick2788
    Nov 27, 2023

    Lorenzo 

    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)
))
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 27, 2023

Lorenzo 

Looks nice. My variant for FillCol

FillCol = LAMBDA(array,
    LET(
    reverse, LAMBDA(arr, SORTBY(arr, SEQUENCE(ROWS(arr)),-1) ),
    down, LAMBDA(arr, SCAN(, arr, LAMBDA(a,v, IF(v="", a, v))) ),
    reverse(down(reverse(down(array))))
    )
)

More correct name will be Fill Down and Up (not fill up and down)

Lorenzo's avatar
Lorenzo
Silver Contributor
Nov 27, 2023

SergeiBaklan 

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