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 2 columns input data  :

Column1Column2
AW
BX
CY
DZ
nullnull
nullE
nullnull
EF
nullnull
nullnull
nullnull
FG
GH
nullnull
nullnull
Hnull
nullnull

 

Desired output :

Column1Column2
AW
BX
CY
DZ
nullnull
nullE
nullE
EF
EF
EF
EF
FG
GH
Gnull
Gnull
Hnull
nullnull

 

Thanks

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

5 Replies

  • 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
    • dhendus's avatar
      dhendus
      Frequent Visitor
      2 years ago

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

        try to buffer to_cols step: 

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

  • 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