Create new query table from existing table with criteria (Employee Leave Records)

[ncbf87]

Hi all,

Problem Statement:

I need a clean table of employee leave application with the following adjustments:

1. Leave Start/End date Adjusted by the Quarter (parameter can be manually set in Power Query), illustrated in Table 3
2. Leave Start/End date Adjusted by the Month (parameter can be manually set in Power Query), illustrated in Table 3
3. Leave Start/End date Adjusted by the Payroll Date (parameter can be manually set in Power Query), illustrated in Table 3
4. To show if an employee took 90 days consecutive leave
5. Cleaned leave application (illustrated in last screenshot below)
   • Let's say I apply for 7/10/2021 to 8/20/2021 as annual leave. I decided to only take 1 day instead and submit to our 3rd party payroll provider for a manual adjustment to make it 7/10/2021 to 7/20/2021. This change will not be reflected in our leave report. However, it will be reflected in the next payrun. This next payrun's leave start/end will be the correct one, and should replace the original leave record

Current:

We're currently doing the above (and more) using VBA, which the file size and time to run grows every time we run it due to increased data size. Currently it takes 2 hours to run it once. Hence, I'd like to seek advice from the community the ideal way to move it onto Power Query.

I'm more than a beginner in Power Query, not necessarily looking for specific steps. But would like to know at a conceptual level, how can I do this.

Data

Table 1: Leave Application Data

Table 2: Paid Leave Data (Payroll)

Table 3: Leave Application - rebuilt (desired output)

Example on Cleaned Leave:

Thanks in advance!

[Ehren]

It might help to break down the problem. To start with, what's the simplest thing you're trying to do? (Ideally this would just be a single step in the process.)

[ncbf87]

1st challenge is to compare Table 1 and Table 2

Table 2 is payrol report and leave data is conclusive. 

If there is no record of a Table 2 row in Table 1, append a row. Otherwise, create a row from Table 2

[Ehren]

Sounds like something you might be able to do using Merge Queries.

[ncbf87]

Thanks Ehren, you're right, a Left/Right Anti join type will solve this challenge after ensuring both table structures are the same.

Problem 2: Adjusting the Leave Start and End date to the year, quarter, and month; factoring in Public Holidays (and rosters, but I can use the solution for public holiday for this)