Movement column between 2 rows

[hitman_421]

Hi, 

I am having trouble adding a movement column in power query using M which will update the status of a person. If that person is no longer a ST, Jules per exemple, it should deduct -1 in the movement column. When the change occurs, it should add +1 in the movement column. 

Thank you !

[v-yetao1-msft]

Hi @hitman_421 

Has your problem been solved ? If it has been solved, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards

Community Support Team _ Ailsa Tao

[v-yetao1-msft]

Hi @hitman_421 

As you said ,if that person is no longer a ST, it should deduct -1 in the movement column. Why the Movement in 2020-03-31 is 1, shouldn't it be -1 ?

In addition ,you said when the change occurs, it should add +1 in the movement column. What do you mean by this change ? Are there any specific fields ? Could you explain it in detail ?

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[collinq]

Hi @hitman_421 ,

In Power Query this is as easy as a Conditional Column.  It will look like this:

Is that what you are needing?

[hitman_421]

Hi,

I tried this solution, but It wouldn't work with my third data line (id #123). The status before is not null which will interfere with the conditional column. Also, I think that the date is also something to take into consideration.

Thank you for your time !

[collinq]

HI @hitman_421 ,

Based on how I read your first comment, I was thinking that your only criteria was the one field.  Ok, then this is still relatively easy.  In Power Query make a Custom Column with an if statement.  Something like : if ([Status] = "x" and [TaskBaselineFinishDate] <> null) then 1 else -1