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

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!