Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Power Query to reference cells

Hi,

 

In new column, I am trying to replicate following excel formula within Power Query, but unable to do it.

=IF(A10<>"",B10,C9)

The formula is "If A10 is not blank, then select value within B10, otherwise select the above cell value of C column"

Any help on this will be great.

Need Help

1 Reply

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    Hi, Anonymous at least 4 options come into my mind:

    # the one when you add a copy of C to your table but shift it 1 row down

    let
    Source = your_table,
    C_shift = List.Buffer({null} & List.RemoveLastN(Source[C], 1)),
    tbl = Table.FromColumns(Table.ToColumns(Source) & {C_shift}, Table.ColumnNames(Source) & {"C_shift"}),
    add_C = Table.AddColumn(tbl, "D", each if [A] <> null then [B] else [C_shift]), 
    remove_shift = Table.RemoveColumns(add_C, "C_shift")
in
    remove_shift

    # the one with index column and C a list

    let
    Source = your_table,
    C = List.Buffer({null} & Source[C]),
    idx = Table.AddIndexColumn(Source, "idx", 1, 1),
    add_d = Table.AddColumn(idx, "D", each if [A] <> null then [B] else C{[idx] - 1}?),
    remove = Table.RemoveColumns(add_d, "idx")
in
    remove

    # the one with index column and C as record with field names = index

    let
    Source = your_table,
    C = Record.FromList(Source[C], List.Transform({1..Table.RowCount(Source)}, Text.From)),
    idx = Table.AddIndexColumn(Source, "idx", 0, 1),
    add_d = Table.AddColumn(idx, "D", each if [A] <> null then [B] else Record.FieldOrDefault(C, Text.From([idx]))),
    remove = Table.RemoveColumns(add_d, "idx")
in
    remove

    # and finally the fastest one - using List.Generate

    let
    Source = your_table,
    r = List.Buffer(Table.ToRecords(Source)),
    g = 
        List.Generate(
            () => [i = 0, row = r{0}, res = row & [D = if row[A] <> null then row[B] else null]],
            (x) => r{x[i]}?  <> null,
            (x) => [i = x[i] + 1, row = r{i}, res = row & [D = if row[A] <> null then row[B] else x[row][C]]],
            (x) => x[res]
        ),
    z = Table.FromRecords(g)
in
    z