Forum Discussion
Payroll incorporations and function changes - People Analytics
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!
2 Replies
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:
