Payroll incorporations and function changes - People Analytics

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: