Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
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
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
10 | |
8 | |
6 | |
6 | |
6 |