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
MVP
Nov 27, 2023

Lorenzo 

And back to this Can SCAN() and BYROW() be combined? - Microsoft Community Hub discussion one of variants which is similar to your one

FillUpDn = LAMBDA(array,
LET(
    fnFill, 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))))
    ) ),
    fillData, LAMBDA(arr,
    REDUCE(
        1,
        SEQUENCE(COLUMNS(arr)),
        LAMBDA(A, i, IF(SEQUENCE(,i) = i, fnFill(INDEX(arr,, i )), A))
    ) ),
    VSTACK(TAKE(array,1), fillData(DROP(array,1)) )
))

Resources