Forum Discussion

dhendus's avatar
dhendus
Frequent Visitor
2 years ago
Solved

Condionally fill down multiple columns

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...
Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago
    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
AlienSx's avatar
AlienSx
Super User
2 years ago
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