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.
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.
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.
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.
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.
