Human Resources (HR) can garner meaningful insights using Office 365, the basic Microsoft Power BI functions, and with straightforward SharePoint lists to hold employee lifecycle data. Over the next 2-3 posts, we’ll watch the HR team build Microsoft Power BI reports and dashboards that are focused on learning more about patterns within the context of employee attrition and employee lifecycle.
In this post we see HR extract data from SharePoint Online and transform/load that data into Microsoft Power BI for the reports and dashboards that are demonstrated in the next blog post.
Data in the Employee Lifecycle SharePoint List
Here’s a screen shot of the SharePoint List that will be used to build out the Human Resources Employee Lifecycle reporting artifacts in Power BI:
*All names are completely fictional
That’s it, with 5 columns of data in the SharePoint List, HR will be able to build meaningful reports in Microsoft Power BI using only the most basic functions in the suite.
Some quick notes about the employee data:
- Every employee has a Department Name, Recruit Date, and an Onboard Date value specified.
- Only those employees who are no longer with he company will have a value in the Offboard Date column.
Import the SharePoint List into Power BI
The first step for Human Resources (HR) is to launch Power BI Desktop.
From there, they switch into the Power Query Editor and select New Source > More… > SharePoint Online List > Connect.
After specifying the URL to the Human Resources Intranet site in the prompt (i.e. that appears after pressing “Connect”), they find the Employee Lifecycle Milestones list. That’s selected within the Power BI Query Editor Navigator panel:
Here is the default view of the list after it loads into the Power Query Editor:
There’s a lot of information that is unneeded, and other data that needs to be transformed – let’s have a look.
Cleaning Up the Power BI Data within Power Query Editor
The first thing Human Resources needs to do is to get rid of unnecessary data. Afterwards, they’ll transform some existing columns as well as create new columns.
Delete Unwanted Data
HR deletes everything except:
- Department Name
- Recruit Date
- Onboard Date
- Offboard Date
They repeat the following steps for every column in the Power Query Editor except those listed above:
- Right click the column header (or select multiple columns at once)
- Choose “Remove” from the column’s context menu
When they finish their table looks as follows:
Convert Dates with Timestamps to Date Only
Human Resources (HR) now transforms all of the Date fields to be Date Only. Note that the field is created in SharePoint as a “Date Only” field, but it is actually stored with the timestamp included, and that is why the full date-time appears in the imported data in Power BI.
Create New Date Columns in the Power Query Editor
Convert Dates to Friendly Reporting Values
Now that the lifecycle milestone dates have been transformed to date-only, Human Resources will create new columns that will again rely on transforming the milestone dates.
- When they go to build reports, HR wants to model their data by month-and-year, not a specific day of the year; the latter is too granular.
- Also, when slicing by month in their reports, they want the English value (January, February, March, etc.) and not the numerical value (1, 2, 3, etc.).
The above steps are repeated for Onboard Date and Offboard Date giving:
Using Power BI Query Editor for Date Calculations
Finally, the last bit of data Human Resources wants in their report are: Time to Onboard and Length of Employment.
Also, not shown in the video, those “Total Days” columns were renamed with a more significant value:
At this point, all of the data has been imported and transformed. In the next post, we’ll watch Human Resources build their reports and dashboards in the Power BI Desktop report builder.