CODEWITHSUNDEEP
excelvbapowerquery
TCritical
TCritical

Reputation: 71

Compare upper cell with lower cell in power query with if & AND condition

Can anyone suggest me how to write this formula (which is shown in image) in Powerquery. The code which i wrote is 

if [Date]<>[previous Date] and [Hrs Reg]<>0 then "No"else if [FinalStatus]="Yes" and [#"G/H"]<=0.08 then "No" else null

Upvotes: 0

Views: 879

Answers (1)

Dreekun
Dreekun

Reputation: 442

By [previous Date], do you mean the previous cell [Date]?

If so, you need to create two index columns, one starting from 1 (you can name it Index), and another starting from 0 (this will be your Previous).

This way you have a corresponding value between the current row and the previous one.

Next, you can Merge the table with itself, using the columns Index and Previous.

After that, you can expand the new table, select the [Date] column, and this will put the previous date in the same row.

The final step is what you already have, just replace [previous Date] with that new expanded [Date], that is actually the previous date.

For reference and better explanation, watch this video by Matt Allington.

Upvotes: 2

Related Questions