Attendance Tracking for Partner Compliance

SLIDE 01

Attendance data

SLIDE 02

Attendance and partner compliance

One of my main priorities within my Compliance Analyst role was to collect the attendance data for our various teams in our department that will contribute to each funded partners overall compliance score

SLIDE 03

Standardizing agency names

Because all of the teams recorded their data different, my first step was to adopt an universal naming convention create a crosswalk that has the Agency ID as the Primary Key and the Agency Name as the Foreign Key

SLIDE 04

Matching attendance records to agencies

Each team had their own separate Excel where they could input their data but because this was a new process, I primary had to conduct the ETL process into the excel sheets from any source they used to capture the data. To try to save time from deciphering the correct agency name manually, if the teams were able to capture the email of the attendees, I was able to extract the agency domain from the email address and cross reference the domains with a crosswalk a former colleague created.

=RIGHT(cell, LEN(cell) - SEARCH (“@”, cell))

=XLOOKUP (cell, Reference cells, Return cells)

SLIDE 05

Combining team data with Power Query

After all of the data has been cleaning and transformed to the current format. A new excel sheet was created that would import all of the teams master attendance sheets using power query. Once all of the sheets were in a created two additional queries that would combine all of the data.