The Central Goal
Create a reliable translation layer that allowed separate business systems to interpret the same workforce records consistently.
Project Overview
Connecting Systems That Did Not Speak the Same Language
Payroll and learning-management platforms depended on the same workforce information, but they did not use job codes in a consistent way.
Differences in naming conventions, formatting, abbreviations, and missing values created mismatches when employee data moved between systems.
I designed a dual-layer solution using MySQL for automated data processing and Power Query for a user-facing review workflow. The solution standardized job codes, detected exceptions, and produced cleaner datasets for downstream systems.
The Problem
Small Data Differences Created Larger System Problems
Inconsistent Codes
The same role could be represented by different codes, abbreviations, descriptions, or formatting conventions.
Unmatched Records
Missing, outdated, or unrecognized values prevented some records from mapping cleanly between platforms.
Manual Corrections
Employees had to identify and resolve mismatches manually, increasing administrative work and the opportunity for error.
Before and After
From Disconnected Values to a Shared Standard
System-Specific Job Codes
- Different codes represented similar roles.
- Naming conventions were inconsistent.
- Missing values required manual research.
- Errors were discovered after import.
- Corrections were difficult to track.
Standardized Mapping Structure
- Each source code mapped to an approved standard.
- Descriptions followed consistent formatting.
- Exceptions were identified before export.
- Clean records were ready for downstream use.
- Mappings could be reviewed and maintained.
Solution Architecture
A Dual-Layer Data Processing Workflow
Import Source Data
Collect job-code and workforce records from the source system.
Normalize With SQL
Clean formatting and apply standardized mapping logic.
Validate Records
Detect missing, mismatched, duplicate, or noncompliant values.
Review Exceptions
Present flagged records through a user-friendly Power Query workflow.
Export Clean Data
Produce a standardized dataset for the destination system.
Automated Processing
Layer One: MySQL
The database layer handled repeatable cleanup and normalization logic. It created a consistent process that could be rerun when updated source data became available.
- Standardized spacing and capitalization
- Normalized job-code formats
- Applied approved code mappings
- Detected duplicate records
- Flagged missing or invalid values
- Produced clean and exception datasets
User Review
Layer Two: Power Query
The Power Query layer gave nontechnical users a familiar interface for refreshing data, reviewing exceptions, and preparing corrected records.
- Imported data into an accessible Excel workflow
- Displayed unmatched and noncompliant records
- Separated clean records from exceptions
- Supported review without direct database access
- Reduced dependence on manual spreadsheet cleanup
- Created a repeatable refresh process
Validation Logic
Catching Problems Before They Reached Another System
The solution did more than transform data. It also identified records that could not be safely standardized without review.
Project Process
How the Standardization Solution Was Developed
Analyze the Data
Compared payroll and LMS records to identify formatting differences, missing values, and conflicting job codes.
Define the Mapping
Created a standardized mapping structure that connected source codes to approved destination values.
Automate Cleanup
Implemented SQL and Power Query transformations to make the process consistent and repeatable.
Validate the Output
Separated clean records from exceptions so unresolved data could be corrected before import.
Project Outcomes
A Stronger Foundation for Connected Systems
Improved Data Integrity
Standardized values reduced ambiguity and made workforce records more consistent across platforms.
Better Exception Visibility
Missing and mismatched values were identified before they became downstream system errors.
Repeatable Processing
The workflow could be refreshed with new source data instead of rebuilding cleanup steps manually.
Accessible Review
Power Query gave users a familiar interface for reviewing exceptions without needing direct SQL access.
Smoother Integration
Clean, standardized records created a more reliable exchange between payroll and learning systems.
Scalable Structure
New job codes and mappings could be incorporated into the existing process as business requirements changed.
Our Role
- Analyzed job-code inconsistencies between systems
- Designed the standardized mapping structure
- Built SQL cleanup and validation logic
- Created the Power Query review interface
- Developed clean-data and exception outputs
- Documented the repeatable refresh workflow
Tools and Skills
This public case study summarizes the project at a high level. Internal employee data, proprietary job codes, company records, and organization-specific system configurations have been omitted or generalized.
Data Standardization
Connected Systems Depend on Consistent Data
When platforms use different names, codes, and formats, a standardization layer can turn disconnected records into reliable information.
Discuss a Data Project