HR - Inventory Movements & Tenure

Overview

In this example, let’s say your work in the HR department of a company. You need to show to your wider team, and your bosses, what staff movements have taken place in the workforce. You realise that in order to show this, you need to create an Inventory Management Table!

You have so far been able to obtain following HR data (something you got from your H.R. ERP).

Click to view large-sized image

You see that every time an Employee’s Country, Office, Department, Job Grade, or Full or Part Time status changes, a new entry is created. This is needed so you can see each important change to an Employee’s profile. (Sadly, not many H.R. driven ERPs can record even this detail, but let’s assume that the one you have, can!) - (You could even optionally add Salary here too, as an extra field to track, if you wanted to!)

Before an Inventory Movement table can be created (your end goal!), you need to transform the data as it stands above, into something else, that will allow you to quickly, and accurately determine:

1. Whether a Employee change (in Country, Department, Job Grade, etc.) is Internal or External to the company
2. What each Employee’s Total Running Tenure is – up to the end of a particular month, or the current date
3. What an Employee’s exact Profile was (in Country, Department, Job Grade, etc.) at a particular point in time (e.g. a particular Month, or Quarter)

To get the data into the right form, a series of intermediary tranformations, and data tables need to be undertaken, and created first. This article explains how to do this, tackling each of the above points, one by one:

Is an Employee Movement an Internal or External Change?

1. Load Data into Power Query

• Starting with the data you have (assuming it is in the above format), import it into Power Query

When loaded, it should look something like this:

2. Group Data & Add Index Columns

• Group the table with the following settings:

• Click OK, and then add the following three Custom Columns. These are different index columns that start from 1, 0, and 2

The data should now look like this:

• Remove all columns except for the ‘User ID’, and ‘Index 3’ columns
• Expand all the fields from the ‘Index 3’ column
• You should now have 3 Index columns that look as follows:

3. Replace any nulls in the End Date Column

• Use the following ‘M’ code to replace any blanks in the End Date column, with the date 31/12/9998:

= Table.ReplaceValue(#"Expanded Index 3",null,#datetime(9998, 12, 31, 0, 0, 0),Replacer.ReplaceValue,{"End Date"})

4. Merge Index Columns to retrieve Previous and Next Dates

• Merge the Query with itself, using the ‘Previous’ and ‘Record’ columns (and ‘User ID’ column, not shown), like shown below:

• Extract the ‘End Date’ field from the new Merged column and call it ‘Previous End Date’. The Data should now look something like:

• Do a similar Merge with the ‘Next’ and ‘Record’ columns (and ‘User ID’ columns, now shown), like shown below:

• Extract the ‘Start Date’ field from the new Merged column and call it ‘Next Start Date’. The Data should now look something like:

• Sort the records, by ‘User ID’ and then ‘Start Date’, as the previous Merge operations may have changed the order of the records
• Remove the ‘Previous’ and ‘Next’ columns as they are now no longer needed

5. Replace any nulls in the Previous End Date and Next Start Date

• Use the following ‘M’ code to replace any blanks in the Previous End Date column, with the date 01/01/1900

= Table.ReplaceValue(#"Removed Index Columns",null,#datetime(1900, 1, 1, 0, 0, 0),Replacer.ReplaceValue,{"Previous End Date"})

• Use the following ‘M’ code to replace any blanks in the Next Start Date column, with the date 31/12/9999

= Table.ReplaceValue(#"Fill Blank Previous End Dates",null,#datetime(9999, 12, 31, 0, 0, 0),Replacer.ReplaceValue,{"Next Start Date"})

• Change the Type of all the Date Fields in the query, with the following M-Code

= Table.TransformColumnTypes(#"Fill Blank Next Start Dates",{{"Start Date", type date}, {"End Date", type date}, {"Previous End Date", type date}, {"Next Start Date", type date}})

6. Add the External and Internal Join and Leave Columns

• With the Previous End Date, and Next Start Dates added to each record, you can now add 4 conditional columns, with the following logic:

The query should now have four additional columns, as follows:

So, now we have some logic to know which changes are External, and which changes are Internal.

• Call the Query ‘Staff Table with Join & Leave Logic’

‘M’ Code: Employee Query

The final ‘M’ code for this query should look like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Inventory_Table"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"User ID"}, {{"Details", each _, type table}}),
    #"Added Index" = Table.AddColumn(#"Grouped Rows", "Index", each Table.AddIndexColumn([Details], "Record", 1, 1)),
    #"Added Index 2" = Table.AddColumn(#"Added Index", "Index 2", each Table.AddIndexColumn([Index], "Previous", 0, 1)),
    #"Added Index 3" = Table.AddColumn(#"Added Index 2", "Index 3", each Table.AddIndexColumn([Index 2], "Next", 2, 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index 3",{"Details", "Index", "Index 2"}),
    #"Expanded Index 3" = Table.ExpandTableColumn(#"Removed Columns", "Index 3", {"First Name", "Last Name", "Gender", "Start Date", "End Date", "Country", "Office", "Department", "Job Grade", "Full or Part", "Record", "Previous", "Next"}, {"First Name", "Last Name", "Gender", "Start Date", "End Date", "Country", "Office", "Department", "Job Grade", "Full or Part", "Record", "Previous", "Next"}),
    #"Fill Blank End Dates" = Table.ReplaceValue(#"Expanded Index 3",null,#datetime(9998, 12, 31, 0, 0, 0),Replacer.ReplaceValue,{"End Date"}),
    #"Merged Queries" = Table.NestedJoin(#"Fill Blank End Dates",{"User ID", "Previous"},#"Fill Blank End Dates",{"User ID", "Record"},"Reordered Columns",JoinKind.LeftOuter),
    #"Show Previous End Date" = Table.ExpandTableColumn(#"Merged Queries", "Reordered Columns", {"End Date"}, {"Previous End Date"}),
    #"Merged Queries1" = Table.NestedJoin(#"Show Previous End Date",{"User ID", "Next"},#"Show Previous End Date",{"User ID", "Record"},"Expanded Reordered Columns",JoinKind.LeftOuter),
    #"Show Next Start Date" = Table.ExpandTableColumn(#"Merged Queries1", "Expanded Reordered Columns", {"Start Date"}, {"Next Start Date"}),
    #"Sort by Users then Start Date" = Table.Sort(#"Show Next Start Date",{{"User ID", Order.Ascending}, {"Start Date", Order.Ascending}}),
    #"Removed Index Columns" = Table.RemoveColumns(#"Sort by Users then Start Date",{"Previous", "Next"}),
    #"Fill Blank Previous End Dates" = Table.ReplaceValue(#"Removed Index Columns",null,#datetime(1900, 1, 1, 0, 0, 0),Replacer.ReplaceValue,{"Previous End Date"}),
    #"Fill Blank Next Start Dates" = Table.ReplaceValue(#"Fill Blank Previous End Dates",null,#datetime(9999, 12, 31, 0, 0, 0),Replacer.ReplaceValue,{"Next Start Date"}),
    #"Changed Date Field Types" = Table.TransformColumnTypes(#"Fill Blank Next Start Dates",{{"Start Date", type date}, {"End Date", type date}, {"Previous End Date", type date}, {"Next Start Date", type date}}),
    #"Added External Join" = Table.AddColumn(#"Changed Date Field Types", "External Join", each if Duration.Days(Duration.From([Start Date]-[Previous End Date])) > 30 then "Y" else "N"),
    #"Added External Leave" = Table.AddColumn(#"Added External Join", "External Leave", each if Duration.Days(Duration.From([Next Start Date]-[End Date])) > 30 then "Y" else "N"),
    #"Added Internal Join" = Table.AddColumn(#"Added External Leave", "Internal Join", each if [External Join] = "Y" then "N" else "Y"),
    #"Added Internal Leave" = Table.AddColumn(#"Added Internal Join", "Internal Leave", each if [External Leave] = "Y" then "N" else "Y")
in
    #"Added Internal Leave"

DAX Alternative

Note the extra columns created above can be added to the original Employee Data Table with Calculated Columns, using DAX instead of transforming the table using ‘M’ code.

Generally, if a column can be pre-computed or pre-processed we prefer using ‘M’ code, but this is not to say that DAX can’t also be used. Most of the time, it’s a matter of preference. Sometimes DAX can actually be much better, particular when you need to run more complex (running total or average) calculations, like shown in this Salary DAX article: HR - Showing Employee Salaries & analysing trends in Actual and Average Running (Pro-Rated) Salaries over time

To find out the DAX formula that can also be used to create the same additional columns to calculate Tenure, click to this Tenure DAX article: HR - Showing Inventory Movements & analysing Total Running Tenure over time. Refer to the section: Calculated Columns.

Cross-Join with Monthly Calendar

To know the exact profile of an Employee (e.g. with Country, Department, Job Grade, etc. details) at a particular point in time (e.g. particular Month, or Quarter), as well as their Running Tenure, we need to build in some more logic.

The steps for this would be as follows:

1. Find Min Date

• Determine the minimum date needed for the Calendar Table, by creating a Query with the following ‘M’ code:

let
    Source = Excel.CurrentWorkbook(){[Name="Inventory_Table"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Start Date", "End Date"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Value", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", let earliest = List.Min(#"Changed Type"[Value]) in each [Value] = earliest),
    Value = #"Filtered Rows"{0}[Value]
in
    Value

• Call the Query ‘Min_Date’

2. Find Max Date

• Determine the maximum date needed for the Calendar Table, by creating a Query with the following ‘M’ code:

let
    Source = Excel.CurrentWorkbook(){[Name="Inventory_Table"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Start Date", "End Date"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Value", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", let latest = List.Max(#"Changed Type"[Value]) in each [Value] = latest),
    Value = #"Filtered Rows"{0}[Value],
    Today = DateTime.Date(DateTime.LocalNow()),
    #"Max Date" = List.Max({Value, Today}, 1)
in
    #"Max Date"

• Call the Query ‘Max_Date’

3. Create a Calendar Table

• Now, using the above two date parameters, create a Calendar Table, using the following ‘M’ code:

let
    End_Date = Max_Date,
    Start_Date = Min_Date,
    
    durationDays = Duration.Days( End_Date - Start_Date ) + 1,
    dates = List.Dates(Start_Date, durationDays, #duration( 1, 0, 0, 0 ) ),
    #"Converted to Table" = Table.FromList(dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Inserted Start of Month" = Table.AddColumn(#"Renamed Columns", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),
    #"Removed Other Columns" = Table.SelectColumns(#"Inserted End of Month",{"Start of Month", "End of Month"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Inserted Year" = Table.AddColumn(#"Removed Duplicates", "Year", each Date.Year([Start of Month]), Int64.Type),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "Quarter", each Text.Insert(Text.From(Date.QuarterOfYear([Start of Month])), 0, "Q"), Int64.Type),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Quarter", {{"Year", type text}, {"Quarter", type text}}, "en-GB"),{"Year", "Quarter"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Year & Quarter"),
    #"Inserted Year1" = Table.AddColumn(#"Merged Columns", "Year", each Date.Year([Start of Month]), Int64.Type),
    #"Inserted Quarter1" = Table.AddColumn(#"Inserted Year1", "Quarter", each Date.QuarterOfYear([Start of Month]), Int64.Type),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Quarter1", {{"Year", type text}, {"Quarter", type text}}, "en-GB"),{"Year", "Quarter"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Year & Quarter - Number"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns1",{{"Year & Quarter - Number", Int64.Type}}),
    #"Inserted Year2" = Table.AddColumn(#"Changed Type1", "Year", each Date.Year([Start of Month]), Int64.Type)
in
    #"Inserted Year2"

The calendar should look something like this:

4. Cross Join with Employee Query

• Next, we need to create a new query that references the earlier Calendar Table, and add a custom column, like so:

With the extra column, the query should now look like this:

• Expand all fields from the new column, to cross join it with the previously created Employee Data query.

5. Create Tenure Columns

Now, we need to create some columns to show the Total Running Tenure of a particular Employee, and the Total Running Tenure of a particular Employee by Job Grade.

• Add the following customer column, to show for each record, the ‘Days in Service’, with the following ‘M’ code:

= List.Max({Number.From([Start Date]), 
List.Min({Number.From(Date.AddDays(DateTime.Date(DateTime.LocalNow()), 1)), Number.From(Date.AddDays([End Date], 1)), Number.From(Date.AddDays([End of Month], 1))})}) - Number.From([Start Date])

In the above calculation, the End Date is set to be always be the lower of: (i) Today’s Date, (ii) the End of the Month, or (iii) the End Date of the particular record. It will though be no lower than the Start Date of the particular record.

• Now, with the ‘Days in Service’ column created, Group the Data with the following settings:

• Expand all the fields from this column, except the ‘Start of Month’ and ‘User ID’ columns (as they are already there, from the Grouping)

The data should now look like this:

• Convert the Running Days in Service column into Years, with the following Customer Column

• Next, remove the ‘Running Days in Service’ column as it is no longer needed
• Now, add a ‘Tenure Bracket’ column, with a custom column, like so:

• Now re-group the Data set again, as follows, but also remembering to group by ‘Job Grade’

• Expand all the fields from the ‘Details’ column, except for ‘Start of Month’, ‘User ID’, and ‘Job Grade’, as they are already there from the Grouping

The last few columns of the Dataset should look as follows:

• Convert the ‘Running Days in Service – by Job Grade’ column into months, by adding the following custom column

• Finally, remove the ‘Running Days in Service – by Job Grade’ column as it is now no longer needed

6. Create Conditional Logic Columns

• Add the following 5 conditional columns, that will later help determine whether an Employee started in a particular month, left in a particular month, or was part of the Head Count at the start or end of the month

• Lastly re-order the columns if you need to.

If you now load the query into a worksheet, you should see the additional columns, added before, and after the Cross-Join operation, as well as the additional rows from the Cross Join, to get to the desired Inventory Management Table.

Click to view large-sized image

7. Create a HR Inventory Management Table

The cross-joined query can now be used as the source for creating a HR Inventory Movement and Tenure Reports, (sample pictures shown further below). Please refer to the following Tenure DAX article: HR - Showing Inventory Movements & analysing Total Running Tenure over time, that details how this can be done by building a Data Model on top of the final cross-joined Inventory table, illustrated in this article.

‘M’ Code: Cross-Join Query

The final ‘M’ code for the cross-join query should look something like this:

let
    Source = #"Monthly Calendar",
    #"Added Custom" = Table.AddColumn(Source, "Custom", each #"Staff Table with Join & Leave Logic"),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"User ID", "First Name", "Last Name", "Gender", "Start Date", "End Date", "Country", "Office", "Department", "Job Grade", "Full or Part", "External Join", "External Leave", "Internal Join", "Internal Leave"}, {"User ID", "First Name", "Last Name", "Gender", "Start Date", "End Date", "Country", "Office", "Department", "Job Grade", "Full or Part", "External Join", "External Leave", "Internal Join", "Internal Leave"}),
    #"Added Days in Service" = Table.AddColumn(#"Expanded Custom", "Days in Service", each List.Max({Number.From([Start Date]), 
List.Min({Number.From(Date.AddDays(DateTime.Date(DateTime.LocalNow()), 1)), Number.From(Date.AddDays([End Date], 1)), Number.From(Date.AddDays([End of Month], 1))})}) - Number.From([Start Date])),
    #"Grouped Rows Total Tenure" = Table.Group(#"Added Days in Service", {"Start of Month", "User ID"}, {{"Details", each _, type table [Start of Month=date, End of Month=date, #"Year & Quarter"=text, #"Year & Quarter - Number"=nullable number, Year=number, User ID=text, First Name=text, Last Name=text, Gender=text, Start Date=date, End Date=date, Country=text, Office=text, Department=text, Job Grade=text, Full or Part=text, External Join=text, External Leave=text, Internal Join=text, Internal Leave=text, Days in Service=number]}, {"Running Days in Service", each List.Sum([Days in Service]), type number}}),
    #"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows Total Tenure", "Details", {"End of Month", "Year & Quarter", "Year & Quarter - Number", "Year", "First Name", "Last Name", "Gender", "Start Date", "End Date", "Country", "Office", "Department", "Job Grade", "Full or Part", "External Join", "External Leave", "Internal Join", "Internal Leave", "Days in Service"}, {"End of Month", "Year & Quarter", "Year & Quarter - Number", "Year", "First Name", "Last Name", "Gender", "Start Date", "End Date", "Country", "Office", "Department", "Job Grade", "Full or Part", "External Join", "External Leave", "Internal Join", "Internal Leave", "Days in Service"}),
    #"Added Tenure Yrs" = Table.AddColumn(#"Expanded Details", "Running Tenure Yrs", each [Running Days in Service] / 365.25),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Tenure Yrs",{"Running Days in Service"}),
    #"Added Tenure Bracket" = Table.AddColumn(#"Removed Columns1", "Tenure Bracket", each if [Running Tenure Yrs] = 0 then "Nil" else if [Running Tenure Yrs] > 0 and [Running Tenure Yrs] <= 0.5 then "0 to 0.5 years" else if [Running Tenure Yrs] > 0.5 and [Running Tenure Yrs] <= 1 then "0.5 to 1 year" else if [Running Tenure Yrs] > 1 and [Running Tenure Yrs] <= 3 then "1 to 3 years" else if [Running Tenure Yrs] > 3 and [Running Tenure Yrs] <= 5 then "3 to 5 years" else if [Running Tenure Yrs] > 5 and [Running Tenure Yrs] <= 8 then "5 to 8 years" else if [Running Tenure Yrs] > 8 and [Running Tenure Yrs] <= 1000 then "More than 8 years" else "Other"),
    #"Grouped Rows JG Tenure" = Table.Group(#"Added Tenure Bracket", {"Start of Month", "User ID", "Job Grade"}, {{"Details", each _, type table [Start of Month=date, User ID=text, End of Month=nullable date, #"Year & Quarter"=nullable text, #"Year & Quarter - Number"=nullable number, Year=nullable number, First Name=nullable text, Last Name=nullable text, Gender=nullable text, Start Date=nullable date, End Date=nullable date, Country=nullable text, Office=nullable text, Department=nullable text, Job Grade=nullable text, Full or Part=nullable text, External Join=nullable text, External Leave=nullable text, Internal Join=nullable text, Internal Leave=nullable text, Days in Service=nullable number, Running Tenure Yrs=number, Tenure Bracket=text]}, {"Running Days in Service - by Job Grade", each List.Sum([Days in Service]), type nullable number}}),
    #"Expanded Details1" = Table.ExpandTableColumn(#"Grouped Rows JG Tenure", "Details", {"End of Month", "Year & Quarter", "Year & Quarter - Number", "Year", "First Name", "Last Name", "Gender", "Start Date", "End Date", "Country", "Office", "Department", "Full or Part", "External Join", "External Leave", "Internal Join", "Internal Leave", "Days in Service", "Running Tenure Yrs", "Tenure Bracket"}, {"End of Month", "Year & Quarter", "Year & Quarter - Number", "Year", "First Name", "Last Name", "Gender", "Start Date", "End Date", "Country", "Office", "Department", "Full or Part", "External Join", "External Leave", "Internal Join", "Internal Leave", "Days in Service", "Running Tenure Yrs", "Tenure Bracket"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Details1", "Running Tenure Mnths - by Job Grade", each [#"Running Days in Service - by Job Grade"] * 12 / 365.25),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Running Days in Service - by Job Grade"}),
    #"Started in Month" = Table.AddColumn(#"Removed Columns", "Started in Month ?", each if ([Start Date] >= [Start of Month] and [Start Date] <= [End of Month]) then "Y" else "N"),
    #"Left in Month" = Table.AddColumn(#"Started in Month", "Left in Month ?", each if ([End Date] >= [Start of Month] and [End Date] <= [End of Month]) then "Y" else "N"),
    #"HC at Start of Month" = Table.AddColumn(#"Left in Month", "Part of HeadCount at Start of Month", each if ([Start Date] < [Start of Month] and [End Date] >= [Start of Month]) then "Y" else "N"),
    #"HC at End of Month" = Table.AddColumn(#"HC at Start of Month", "Part of HeadCount at End of Month", each if ([Start Date] <= [End of Month] and [End Date] > [End of Month]) then "Y" else "N"),
    #"HC at Start or End of Month" = Table.AddColumn(#"HC at End of Month", "Part of HeadCount Start or End of Month", each if [Part of HeadCount at Start of Month] = "Y" or [Part of HeadCount at End of Month] = "Y" then "Y" else "N"),
    #"Reordered Columns" = Table.ReorderColumns(#"HC at Start or End of Month",{"Start of Month", "End of Month", "Year & Quarter", "Year & Quarter - Number", "Year", "User ID", "First Name", "Last Name", "Gender", "Start Date", "End Date", "Country", "Office", "Department", "Job Grade", "Full or Part", "External Join", "External Leave", "Internal Join", "Internal Leave", "Days in Service", "Running Tenure Yrs", "Tenure Bracket", "Running Tenure Mnths - by Job Grade", "Started in Month ?", "Left in Month ?", "Part of HeadCount at Start of Month", "Part of HeadCount at End of Month"})
in
    #"Reordered Columns"