Return to Answer

added a way to get back to OP's reporting format

Source Link

edited Jun 29, 2020 at 16:44

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.

Source Link

created Jun 29, 2020 at 13:50

FlexYourData

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:

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