I am a fractional CFO who is familiar with Power Query and its capabilities but have not been able to apply it in practice. One opportunity I see for PQ is to automate a monthly payroll entry I have to make to "close the books" for multiple clients. The client has many departments and close to 100 different pay codes on any given check run, all of which need to be mapped to unique general ledger accounts to create the monthly journal entry. The source data for this entry from the payroll system is formatted consistently each month but the process is currently very manual (takes 3 - 5 hours per month). Below is the current process:
1) Download monthly payroll journal detail from payroll system (screenshot example of original source data below - red text indicates the different pay codes referenced in the next step):
View attachment 110376
2) Manually insert columns to add up different pay codes into one amount (for example, Regular, Overtime, and PTO pay have to be added into one manually created "wages" field; multiple different types of bonuses have to be added into one "Bonus" field, etc, etc. (wage example screenshot below). In this example, columns R - AF are added up in to "total Payroll wages", column AG.
3) Via SUMIFS, each unique Wage / department combo is mapped to a template with general ledger account numbers / accounts. For example, all "General Administration" employee wage amounts from the screenshot above are added together via SUMIFS and mapped to one general ledger account (note that totals in the screenshot below don't tie to totals above - only shown for example).
4) Once all accounts in the mapping template in screenshot 3 above are populated and verified to be accurate / complete, the entry is complete and uploaded to our general ledger system.
The above steps are what I'm hoping to automate via Power Query, such that I create the steps and then am able to quickly refresh month after month with new payroll data
1) Download monthly payroll journal detail from payroll system (screenshot example of original source data below - red text indicates the different pay codes referenced in the next step):
View attachment 110376
2) Manually insert columns to add up different pay codes into one amount (for example, Regular, Overtime, and PTO pay have to be added into one manually created "wages" field; multiple different types of bonuses have to be added into one "Bonus" field, etc, etc. (wage example screenshot below). In this example, columns R - AF are added up in to "total Payroll wages", column AG.
3) Via SUMIFS, each unique Wage / department combo is mapped to a template with general ledger account numbers / accounts. For example, all "General Administration" employee wage amounts from the screenshot above are added together via SUMIFS and mapped to one general ledger account (note that totals in the screenshot below don't tie to totals above - only shown for example).
4) Once all accounts in the mapping template in screenshot 3 above are populated and verified to be accurate / complete, the entry is complete and uploaded to our general ledger system.
The above steps are what I'm hoping to automate via Power Query, such that I create the steps and then am able to quickly refresh month after month with new payroll data
