Referencing cell above in power query custom column

Question

I'm working on converting an Excel process to a power query custom column, and the way it currently works is by referencing the cell above to write to current cell.

The Excel formula is =IF(G2<>"", IF(I2<>"",I2,""),H1) Which would be entered in H2 in this example. It essentially says when I have cells with values to the left, and the right of me, I will write the value of the cell to the right, until I see a cell on the left which isn't partnered up with a cell on the right, and go back to null. This runs through a few thousand rows and may be triggered a couple dozen times to patch tiny holes. The end result essentially looks like this, the center column is my new data (I'm using zeros instead of "" or null for visual sake):

1. 0 0 0 - Write null
2. 0 0 0 - Write null
3. x 1 1 - Write value to the right
4. 0 1 0 - Write value above
5. 0 1 0 - Write value above
6. x 0 0 - Write null
7. 0 0 0 - Write null

Right now I have to write the formula before executing my query chain, I'd like to incorporate this step into a custom column, but I have yet to find a solution. I am a total newb to m code and power query, and stack overflow, but so far I have been able to execute each step I needed except for this one.

Answer 1

My suggestion would be a different approach:

The query below is based on Table1 with columns G, H, and I, which creates the first 2 steps.

Next column H is removed.

A new column H is added, initially with the value from I if both G and I are <> null, else if G = null then a blank (will be null later on).

Next the new column H is filled down.

Next "" is replaced with null.

Finally the columns are reordered.

Edit: all steps done via the UI; the formula for the new column was entered in the formula box (without "each ") after choosing "Add Custom Column"

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"G", type text}, {"H", type any}, {"I", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"H"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "H", each if [G] <> null and [I] <> null then [I] else if [G] <> null then "" else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"H"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Down","",null,Replacer.ReplaceValue,{"H"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Replaced Value",{"G", "H", "I"})
in
    #"Reordered Columns"

Answer 2

You can add an index column and then access the cell at the current index minus 1. For example, if you added an index column Index and named that step Table, then to access the value in column Column in the previous row you would this expression in your custom formula:

if [Index] > 0 then Table[Column]{[Index] - 1} else some_default_value

some_default_value would be whatever you want the first row's cell to be. In your case, this would probably be null. We need the if statement because there's no row -1 in the table.