Designing a mass attendance system with Excel and PowerQuery

Question

I am building an Excel spreadsheet for tracking a large number of personnel's daily attendance records. All data examples shown here are fictional, including the data structure. Only the gist of the data structure is shown, for illustrative purposes, as I am working on sensitive personal data on heavily locked down computers (i.e. no Internet, any form of scripting like VBA is banned, only Microsoft Office suite to manipulate data).

The main table for containing personnel records looks something like:

Master List

ID     Name     Home Address         Phone Number   Remarks
1      Alice    12 Somewhere Rd.     123456         remark 1 goes here
2      Bob      23 Someplace Dr.     234567         remark 2 goes here
3      Charlie  34 Somewhen Blvd.    345678         remark 3 goes here

As one can see, not all the information is required during attendance taking. As such, I perform a Power Query to slim it down to the essentials

Attendance

ID     Name      Remarks
1      Alice     remark 1 goes here
2      Bob       remark 2 goes here
3      Charlie   remark 3 goes here

After that, I appended the attendance entries to the right of the table.

Attendance

ID     Name      Remarks               2020/6/25   2020/6/26  ...............
1      Alice     remark 1 goes here    P           P          ...............
2      Bob       remark 2 goes here    P           VL         ...............
3      Charlie   remark 3 goes here    P           P          ...............

Finally, I set up a self referencing table such that upon every refresh of the Attendance table, the attendance entries are preserved in their original rows, meaning that there would not be any problems if I insert a personnel somewhere in the middle.

However, I quickly ran into an issue when I realized I needed to tag every single special attendance entry (e.g. medical leave) with details regarding the leave itself. I initially tried adding comments onto the attendance cells directly, but realized that the comments does not follow the table generated by the Power Query (i.e. it stays in the original absolute cell location, meaning that an insertion of a new person will throw the bottom half of the table out of alignment by exactly one row).

I later attempted to use hyperlinks to link each attendance entry to a separate table which contained the remarks/details about the leave, but the link became unclickable after a query refresh.

How should I go about designing a remarks system for a per-day-per-person attendance system, and is my approach in the start where I query the master list, reduce the columns and then append more columns even the right approach to begin with?

Thank you!

Answer 1

is my approach in the start where I query the master list, reduce the columns and then append more columns even the right approach to begin with?

It's one way of doing it. And I think you could probably achieve your space for remarks simply by adding a remark column for each date, and then following the approach you've already taken to get a self-referential table. Something that looks like this:

Personally, I wouldn't do that, because when the questions come (as they always do) like "Can you tell me how many absences Alice and Bob had together in the past 30 business days?" or "Can you tell me the average number of absent days?" and so on, they will be more difficult to address (not that difficult to be fair, but still).

So I would prefer to use normalized data for input, which can then be pivoted for reporting.

With that in mind, I started with two tables:

1. people
2. dates

The Include column uses this formula (the reason will become clear later):

=[@Dates]<=TODAY()

I created a query on each of those tables. On the dates query, I filtered Include=TRUE and then removed the Include column. Then I added a custom column to the people query to refer to the dates query. Like this:

You can see that it's added the third column to the people query. Expanding that gives a nice list of all dates for all people (this is essentially a cartesian join):

I load this to the workbook and add two columns - Attendance and Remark. The benefit of this is that you can now add as many columns as you want to annotate or mark-up each person-day.

At this point, I read the link to the page you referenced in your post. At first I got it wrong. Then I got it wrong again. Then I realised how it actually worked and ended up with a self-referential table against which I could now edit my attendance and my remark and refresh for new employees with everything staying in the right place.

An additional benefit of this is that each day when I open the workbook and refresh the query, a new set of rows is automatically added to the query for each employee for the current day (this is ultimately the purpose of defining that Include column in the dates table).

EDIT:

You can use PowerQuery to convert the data into various reporting formats, including the format of your 3rd table in your post.

Create a query on the end result of my answer above, then do this:

Select Attendance and Remark, then use Transform>Any Column>Unpivot columns>Unpivot only selected columns:

You'll notice that this action removes any null cells. If you want to retain those, you'll need to fill them before using Unpivot. That can be done with Replace Values from null to whatever you prefer.

After unpivot, I have columns Attribute and Value. Now I want to select date.Dates and Attribute, right-click and use Merge Columns. I set the separator to " - " (you can use whatever you prefer of course) and I end up with a single column representing the date and the type of data it refers to (attendance or remark).

I then select this new merged column and use Transform>Any Column>Pivot column and configure it like this:

The end result is the format you were after. Again, if you want this report to contain all people whether or not their attendance has been entered or not, you'll need to set a dummy value in the Attendance column before using Unpivot.

This all may seem like going around the houses somewhat if you'll forgive the colloquialism. Generally speaking I'd encourage anyone to think about the ways this data is going to be used before settling on a solution. It's been my experience that having the data normalized to begin with (as I've proposed) and then building reports from that table is a more sustainable method and offers more flexibility.