Problem
Raw EPMIS inventory exports required manual filtering, sorting, and formatting before they were usable for management review. Staff across 25 hospital zones were spending recurring cycles on the same transformation work — every reporting period.
Approach
Build a pipeline that ingests raw exports, applies all cleaning and validation rules, and produces a formatted Excel report in one execution. One-click = audit-ready output.
Implementation
Data cleaning Stripped trailing spaces, removed blank rows, filtered system-generated summary rows that weren't real inventory entries.
Facility standardization Resolved inconsistent naming conventions across hospital profiles — shorthand variants, suffix tags, typos — and mapped every record to a canonical facility name from a reference table.
Category mapping Prefix-based matching to assign inventory records to operational service groups: Facilities Engineering, Clinical Logistics Services, and others.
Exception handling
Records with missing category mappings were automatically captured into an Unmapped Items worksheet. Non-warehouse and in-transit records were routed to an Excluded Rows worksheet — every exclusion is traceable.
Report generation Dynamic summary grid calculating inventory values by hospital and service line. Missing combinations filled with zero. Bold headers, consistent number formatting, auto-adjusted column widths.
Reconciliation sheet Totals from processed records vs. excluded values — confirms every source record is accounted for before the report is delivered.
Outcome
A validated summary across all 25 regional zones, formatted and reconciled, ready for management review. Processing time dropped; the transformation is now deterministic and repeatable.