Payroll incorporations and function changes - People Analytics

[Anonymous]

People Analytics Problem
I'm having some trouble trying to find a Query that indicates me the movements of the staff in the system. The variable that I need to consider is ROLE_ID (the primary key regarding the current role of the people in the system). 

My table looks like this:

 

What I want to do is to add a column that shows me when a person initiates a new role in the system, and also when someone changes a previous role.
Is important to clarify the modeling  of the data. To show how the diferrent payrolls evolve each month, I assemble every one of them using Append in Power Query, so this is what it looks like: 

As you can see, I want to highlight that KEVIN LOW is a new incorporation in the system and that MARY ANN change her role for another one. 

I'm really lost here, if you can help me I'll very grateful!

[Anonymous]

Hi @Anonymous , hello MFelix, thank you for your prompt reply!

Is there any progress on this issue?

If you find any answer is helpful to you, please remember to accept it.

It will help others who meet the similar question in this forum.

Thank you for your understanding.

[MFelix]

Hi @Anonymous ,

For this you need to pick up the previous month value in order to check the status of the role try the following code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZHBbsIwDIZfJeoZKakDWXs0qVkzSoKcjGlDvP9rLO7QJBiHSYMdEtuS/f3On+OxAadNq8HAslk0rX2SMOcvKZPisMZcC0+0L5IU8mMMPmCsxYY4YhzoQ2ZAm6Vu+941p8U1tu+stNSzQ35XGGXa78d6D8mXxDV5fi2BmIXVVlYnw/YnC5yVsBLAhIyKchlJAJQ9TrhJwsZhF2LIhbGEAyk/EW9Fg6tEnjus0cYK1HxrwGNsgDvaAP9pA7huXrueLR1CVFN6u7HwJez8UQITsLu/p5fYL0/tbz01q/N7b6z4IE+vNP7g6ekT", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Payroll_Date = _t, Person_ID = _t, Role_ID = _t, Name = _t, SCALE_POSITION = _t, POSITION = _t, HOSPITAL = _t, DATE_ROLE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Payroll_Date", type text}, {"Person_ID", Int64.Type}, {"Role_ID", Int64.Type}, {"Name", type text}, {"SCALE_POSITION", type text}, {"POSITION", type text}, {"HOSPITAL", type text}, {"DATE_ROLE", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Person_ID", Order.Ascending}, {"Payroll_Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Person_ID"}, {{"Details", each Table.AddIndexColumn ( _, "Index",1,1,Int64.Type ), type table [Payroll_Date=nullable text, Person_ID=nullable number, Role_ID=nullable number, Name=nullable text, SCALE_POSITION=nullable text, POSITION=nullable text, HOSPITAL=nullable text, DATE_ROLE=nullable date, Index=nullable Int64.Type]}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Person_ID"}),
    #"Expanded Details" = Table.ExpandTableColumn(#"Removed Columns", "Details", {"Payroll_Date", "Person_ID", "Role_ID", "Name", "SCALE_POSITION", "POSITION", "HOSPITAL", "DATE_ROLE", "Index"}, {"Payroll_Date", "Person_ID", "Role_ID", "Name", "SCALE_POSITION", "POSITION", "HOSPITAL", "DATE_ROLE", "Index"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Details", "Index.1", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let 
 a =  (if [Index] = 1 then null else #"Expanded Details"[Role_ID]{[Index.1] - 1}),
 source = 
 if a = null then "New" else if a = [Role_ID] then null else "New Role"

in

source),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1"})
in
    #"Removed Columns1"

The result is below:

Since I have no data before the January they show all has new: