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
dufoq3's avatar
dufoq3
Community Champion
2 years ago

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