Office 365 and SharePoint – Meaningful Human Resources (HR) Insights – Prepping for Power BI Dashboards on Employee Lifecycle

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

O365 SharePoint Employee Lifecycle Milestones list for Power BI.

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.

O365 SharePoint Power BI select list.

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:

O365 SharePoint Power BI select list from options.

Here is the default view of the list after it loads into the Power Query Editor:

O365 SharePoint Power BI list loaded default.

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:

  • Id
  • Title
  • 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:

  1. Right click the column header (or select multiple columns at once)
  2. Choose “Remove” from the column’s context menu

O365 SharePoint Human Resources deleting Power BI column.

When they finish their table looks as follows:

O365 SharePoint Employee Lifecycle table after deleting columns.

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:

O365 SharePoint Power BI date transforms completed.

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.



Categories: Business, Business Management, Human Resources, Office 365 and O365, Power BI, SharePoint

Tags: , , , ,

Leave a Reply