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
