M Language check if value in a column is the same in next row

[samravp]

Hi All

I have this table, which tracks changes to employee status,

and I sorted values of column named [EmployeeID] in ascending order. I am looking for M query that checks if value in column [EmployeeID] is same in next row (meaning that change relates to same employee), then create a column named [EndDate] to show when that [StatusAfter] ended and a column named [CurrentStatus] to show if that status is current.

After this I would merge queries with same table to end up with my desired table, which would be like below;

How can i do that?

[serpiva64 · Accepted solution]

Hi,

I have changed 2 dates to try (i don't know if it is true).

refer to fantastic video of Goodly for knowledge

https://www.youtube.com/watch?v=IGF2-qfzDQs

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!

[serpiva64]

Hi,

probably you have some problem in your example.

What about ID26 where the current is not the last one?

[MAwwad]

You can use the following M code to achieve the desired output:

1. Load the table into Power Query Editor
2. Add an index column to keep track of the row number
3. Sort the table by [EmployeeID] and [EffectiveDate] columns in ascending order
4. Add a custom column with the following formula:

= if [EmployeeID] = Table.Column([#"Added Index"], "Index"){[Index]+1} then Date.AddDays([EffectiveDate], -1) else null

This formula checks if the current [EmployeeID] value is the same as the [EmployeeID] value in the next row. If it is, it subtracts one day from the [EffectiveDate] to get the [EndDate], otherwise it returns null.

1. Add another custom column with the following formula:

= if [EndDate] = null then "No" else if [EffectiveDate] = List.Max(List.Select(Table.Column([#"Added Index"], "EffectiveDate"), each _ <= [EffectiveDate])) then "Yes" else "No"

This formula checks if the [EndDate] is null, meaning the current row is the latest status for that employee, and returns "Yes" for [CurrentStatus]. Otherwise, it checks if the [EffectiveDate] is equal to the maximum [EffectiveDate] value for that employee, and returns "Yes" for [CurrentStatus] if it is, otherwise it returns "No".

1. Remove the [Index] column and any other unnecessary columns.
2. Load the transformed table into the data model.