Overwrite columns with logic - PowerQuery

Question

I have the following in my Power Query. I would like to overwrite the Enter/Exit column.

The logic that I am looking for is for every "End" to follow with a "Commence" immediately.

Say for Time interval 7 to 9, it would ideally be (7) End, (8) Commence, (9) null

Any idea how I can do this?

Answer 1

See if this works for you in powerquery

Add column .. index column ... from 0...

Add column ... custom column .. with formula:

= try if #"Added Index"{[Index]-1}[Exit]= "End" then "Commence" else if #"Added Index"{[Index]-2}[Exit]= "End" then null else if [Exit] = null then [Enter] else [Exit] otherwise if [Exit] = null then [Enter] else [Exit]

Full sample code

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Enter/Exit", each try if #"Added Index"{[Index]-1}[Exit]= "End" then "Commence" else if #"Added Index"{[Index]-2}[Exit]= "End" then null else if [Exit] = null then [Enter] else [Exit] otherwise if [Exit] = null then [Enter] else [Exit])
in #"Added Custom"