Condionally fill down multiple columns

[dhendus]

I want to condionally fill down multiple columns (approximatively 200 columns) as below :
When the value's column is "E" => begin to fill down, and stop when the value "H" is reached.

Example of 2 columns input data  :

Column1	Column2
A	W
B	X
C	Y
D	Z
null	null
null	E
null	null
E	F
null	null
null	null
null	null
F	G
G	H
null	null
null	null
H	null
null	null

Desired output :

Column1	Column2
A	W
B	X
C	Y
D	Z
null	null
null	E
null	E
E	F
E	F
E	F
E	F
F	G
G	H
G	null
G	null
H	null
null	null

Thanks

[dufoq3]

Hi @dhendus, different approach here. Test speed and let me know:

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpXitWJVnICsiLALGcgKxLMcgGyosAsIANGu6IIuAIZbuhKUGg3IMMdzHIHsjywqvFAF/DEYponioAXkoyXUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    // You can probably delete this step.
    ReplaceBlankToNull = Table.TransformColumns(Source, {}, each if _ = "" then null else _),
    Transform = List.Accumulate(
    List.Buffer(Table.ColumnNames(ReplaceBlankToNull)),
    #table({"_Temp_"}, {{null}}),
    (state, current)=> Table.AddColumn(state, current, each
                [ a = Table.SelectColumns(ReplaceBlankToNull, current),
                  b = Table.Group(a, {current}, {{"All", each _, type table}}, GroupKind.Local, (w,u)=> Byte.From( (Record.Field(w, current) <> "E" and Record.Field(u, current) = "E") or (Record.Field(w, current) = "E" and Record.Field(u, current) = "H") ) ),
                  c = Table.AddColumn(b, "Filled", (x)=> if Record.Field(x, current) = "E" then Table.FillDown(x[All], {current}) else x[All], type table),
                  d = Table.Combine(c[Filled]),
                  e = Table.Column(d, current)
                ][e], type list)
),
    RemovedColumns = Table.RemoveColumns(Transform,{"_Temp_"}),
    ToTable = Table.FromColumns(Table.ToRows(RemovedColumns){0})
in
    ToTable

[AlienSx · Accepted solution]

let
    Source = your_table,
    to_cols = Table.ToColumns(Source),
    g = (lst) => 
        [st = [E = true, H = false],
        gen = List.Generate(
            () => [i = 0, c = lst{0}, fd = c = "E"],
            (x) => x[i] < List.Count(lst),
            (x) => 
                [
                    i = x[i] + 1, 
                    c = if x[fd] then lst{i} ?? x[c] else lst{i}, 
                    fd = Record.FieldOrDefault(st, c ?? "", x[fd])
                ],
            (x) => x[c]
        )][gen],
    tra = List.Transform(to_cols, g),
    to_tbl = Table.FromColumns(tra)
in
    to_tbl

[dhendus]

That's what I'm talking about, thank you.
Unfortunately, I have memory issues when loading data, in step "Table.FromColumns(tra)" (can not load my data, I've been waiting for an hour and yet not finishing) 
can you suggest something else for this step ?

[AlienSx]

try to buffer to_cols step: 

to_cols = List.Buffer(Table.ToColumns(Source))

[dhendus]

Thanks, it dropped to 10 min 🙂