Our Tips

My son and I cleaning a cow stall.
Hope that you also had a great summer! I traveled to visit friends and family. One of my stops was at a dairy farm. Amazing how doing someone else’s job for one day can be so much fun: fixing a tractor, feeding the calves, milking cows with automated machinery, etc. It’s very impressive to see how much technology is involved in running a farm, and yet my friend was using a 40-year-old punch card system to collect the employees’ timestamps. There’s nothing wrong with that; it works quite well. Every week, he manually computes the hours worked by his employees and calculates the net wage to write the pay checks. However this is, as he puts it, one of his most annoying weekly task.

Now I saw an opportunity to show him how much my job can be fun and useful as well!

Power BI is mainly used by large organizations to build complex dashboards: combine multiple data sources, aggregate data and refresh results effortlessly. On a smaller scale its functionalities can do miracles. Using Power BI combined with Google Forms and Sheets, I built a system to collect employees’ timestamps and compute weekly pays.

The first step was to replace the mechanic punch card system with a tablet – any tablet that can run google sheets and can connect to WIFI. The tablet displays a Google Form entitled Punch. The form has two questions: select your name in the dropdown list and select In or Out.

 

A screenshot of the Punch Google form

 

The timestamps are collected in a Google Sheet. My friend, the boss, has access to the sheet to make adjustments when an employee enters errors or forgets to enter a punch. What’s great about Google Sheets is that it tracks versions. We use the version history to track the changes made and who authored the change. In our case, it’s either from the Punch Form or from the boss.

Timestamps are then imported in Power BI. The next step is to validate daily that the employees punched in and punched out each work shift. With Power BI Mobile, my friend receives an alert in the morning on his cell phone (because he’s a morning kind of guy) if an employee made a double entry or forgot a punch. The error can easily be found on the Timestamps Google Sheet and fixed manually.

 

Screenshot of the alert on punch errors: showing 0 errors for yesterday.

 

As an added bonus, the employees’ schedule is also a Google Sheet and is imported into Power BI. Before issuing the pay cheques, the timestamps can easily be validated against the schedule for each employee. In Power BI Service, a table compares the punched hours with the scheduled work periods. The table can be filtered by employee and week using slicers.

 

Screenshot of a table listing scheduled work periods and worked hours per day, for a selected week and selected employee

 

Finally, the pay is calculated in Power BI Service and pay cheques can be issued. The employees’ hourly wages and 2019 deductions parameters where also imported using a Google Sheet. Another bonus: With Power BI Mobile, a screen capture of the weekly pay can be emailed to the accountant. Optionally, a table checks once more for punch errors for the whole week, for each employee.

 

Screenshot of a table calculating the weekly pay for each employee : hourly wage time worked hours minus deductions equals net pay

 

A neat solution, with no monthly fee. Using Power BI on such a small scale only requires a free power bi account. Power BI allowed me to :

  • Combine multiple data sources: Google sheets for timestamps, work schedule, pay calculation parameters
  • Aggregate data: Sum worked hours per week, per employee to calculate the pay
  • Refresh results effortlessly: scheduled daily refresh of data and alert once day if error factor is greater than zero.