Enrollment
What coverage is active?
BENEFITS OPERATIONS / REAL PROJECTComplex benefits.
Clarity, on repeat.
Power Query automated the MetLife bill. I connected census preparation, benefit calculations and exception review, then carried the approach into broader Alteryx work and Power BI reporting.
CensusRulesClarity
SELF-BILL + EXCEPTION CONTEXTMY CONTRIBUTIONAutomation design · team leadership · reporting
THE PRACTICEPower Query → Alteryx → Power BI
PROJECT CONTEXTDelivered at Lockton
THE METLIFE WORKFLOWExplore the structure · no client records
01 / SOURCE
Bring the context together.
BenSelect census exports enter a month-based workflow. Demographics and benefits are prepared separately, then joined.
BenSelect censusBilling month + source folder
SOURCEPrepare the selected month
Demographics
Age & location
Compensation context
Deduction frequency
Benefits
Coverage elections
Effective dates
Benefit amounts
Join the related records
A connected benefit recordEnriched with location and reference mappings
Select a stage to follow the work. Calculations and source data stay private.Stage 1 of 5: Source
02 / THE CHALLENGEBeyond a balanced total.
Beyond a balanced total.
A bill you can explain.
A self-bill is a calculation, not just a list. One client’s coverage crossed different benefit rules. A missing detail could change the result—and leave the analyst explaining a costly adjustment next month.
Age & pay
Which bands and limits apply?
Work location
Which class and rate apply?
Benefit rules
What belongs on this bill?
The hard part was keeping these relationships correct as the population changed.
GROWING CENSUS · CHANGING ELECTIONS · REPEATABLE LOGIC03 / THE BUILDAutomate the work.
Automate the work.
Keep the judgment.
Power Query came first. My HRO presentation at State of the Companies opened the Alteryx opportunity; the following year’s SOC included the two-week cohort challenge. I project-managed a small analyst team a few months later.
POWER QUERY / ORIGINAL WORKBOOKMake the dependencies explicit.
Month-based inputs feed joins, mappings, benefit calculations and itemized output. Separate checks expose missing employer-paid benefits and location classes, even when the totals reconcile.
Month + sourceJoined census
Based on the supplied Power Query structureLifeDisabilityOther benefits
Billing detailException queues
THE WIDER PRACTICEGive the team room to run.
The cohort’s reusable analytic app kept client- and vendor-agnostic logic behind a simple interface. Later manager opt-in adoption brought selected workflows into Alteryx, which processed larger datasets faster in my experience.
Selected workflow ASelf-billBackground run
Selected workflow BList billBackground run
Selected workflow CList billBackground run
AN EXCEPTION IS A HANDOFFFind the issue.
Find the issue.
Keep the path forward clear.
Hundreds of missing employer-paid coverage flags needed investigation. An acquired group was legitimately ineligible; duplicate HR profiles were actual source errors. The client owned corrections.
- 01Automation identifies
Check expected coverage and source quality beyond whether the bill reconciles.
- 02Confirm the reason
Document valid eligibility exceptions; ask HR or payroll to correct duplicate or incomplete profiles.
- 03The analyst reviews
Rerun the workflow and review the result. Preserve original files and the reason for each unresolved item.
04 / THE IMPACTLess processing.
Less processing.
More perspective.
A more reliable workflow changed how the team used its time and how we approached the client: with the issue, the explanation, and the next action.
Time returned
One monthly MetLife workflow went from about nine hours to twenty minutes using Power Query.
Capacity multiplied
Background runs supported multiple bills. The approach extended to simpler list bills and other vendors.
Confidence earned
Clear exceptions supported constructive conversations and helped avoid costly downstream surprises.
The approximately nine-hour-to-twenty-minute MetLife result is my firsthand Power Query account. Broader Alteryx adoption is a separate part of the story.
CLOSE THE LOOPA finished bill
A finished bill
became a useful signal.
I organized census and billing files so Power BI could reference client folders dynamically, without changing the source files. Trends raised questions earlier. Leadership adopted the dashboard to see across the practice.
Explore the reporting storyCensus + billing folders
Reference · compare · reviewConcept illustration · no measured valuesAnalyst view → leadership view
LOOK CLOSER
The evidence behind the story.
The implementation and the delivery experience have different sources. Keep both in view.
01What the original Power Query files demonstrate
The supplied template contains 52 query definitions, including parameters and helper queries. A month-based source selection feeds demographic and benefit transformations, joins to location and rate reference tables, benefit-specific itemized outputs, and separate Missing Location Class and Missing ER Benefit queues. This describes the saved structure, not a new calculation or desktop Excel acceptance test.
02Power Query first, then the Alteryx opportunity
The approximately nine-hour-to-twenty-minute MetLife result came from Power Query. I presented my HRO automation approach at State of the Companies and trained analysts. That work opened an opportunity to participate in the Alteryx rollout. MetLife was a candidate for Alteryx; the timing result does not establish that it migrated.
03Two weeks to build something reusable
At the following year’s SOC, a cohort and I selected an impactful HRO billing use case, learned Alteryx Designer, built the solution and presented it to executives within two weeks. A simple analytic-app interface exposed reusable client- and vendor-agnostic processing while keeping the denser node logic underneath. A few months later I project-managed a small analyst team. Managers opted selected workflows into this level of automation; larger datasets processed faster in Alteryx in my experience.
04A reconciled bill was not the end of validation
The error tabs flagged hundreds of employees missing basic life or other employer-paid coverage. Investigation showed that an acquired group did not qualify, so those records became documented eligibility exceptions. Other flags revealed duplicate HR profiles left in the source system. I separated business exceptions from data errors and gave the client an explanation and correction path; I did not silently rewrite the original data.
05Carry business rules and reporting forward
Later, I used ChatGPT and Microsoft Copilot to translate established Power Query M logic into preconfigured Alteryx nodes through XML. I also organized census and billing folders so Power BI could reference them dynamically without changing source files. The reporting grew from an analyst view into a leadership view across the practice.
06Where the automation stops
Client HR and payroll owned source corrections where our platform access was limited. A generated bill still needed review. Broader Alteryx background runs were manually initiated. Exact SOC years, practice-wide migration and automatic orchestration are not inferred from the retained workbook.
07What you can explore here
The diagrams use actual workflow relationships with abstract visual marks. They contain no employee-level examples, proprietary calculation formulas, live rates, or downloadable workbooks. No interaction sends data to Lockton, MetLife, or a benefits platform.
Original workbooks remain private. This page shares the approach, not employee records or proprietary files.