The challenge
A water-quality engineer manually corrected laboratory results and copied them into Excel to produce basic reports. Inconsistent labels, spelling errors, and misclassified results required repeated attention. Preparing the data consumed substantial effort and limited the scope of analysis and access to reporting.
My role
I designed and built the data integration and reporting solution, working with the water-quality engineer to turn recurring manual corrections into reusable rules and develop reporting suited to the team's monitoring needs.
The approach
Laboratory files were placed in shared folders and processed through automated integration workflows. Conditional mapping rules standardized labels and categories, accounting for source and date range where needed. The combined dataset was enriched with sampling locations, metric significance, thresholds, and other reference information. Scheduled or on-demand processing refreshed the reporting data, replacing repeated copying, pasting, and correction of individual files. Power BI made the resulting reports and analysis available online to authorized users.
Implementation
The solution began as a minimum viable product running alongside the existing manual process. It replaced basic reporting within approximately one month. Further components were automated incrementally, and by month two the team had retired the previous manual reporting process. Capabilities continued to expand during operational use.
Results
The team gained a comprehensive, standardized dataset spanning multiple laboratories, with richer context for interpreting results and surfacing critical issues quickly. Authorized users gained direct access to online reporting and analysis. Reports identifying inconsistent laboratory labels also provided a basis for feedback to laboratories, helping address data-quality problems at their source.
Further development
The roadmap included direct retrieval of laboratory data through FTP or other online sources to reduce the remaining manual effort of obtaining files.