The client is a women-owned STEM company that provides a water treatment solution to permanently remove toxic PFAS contaminants and turn them into harmless byproducts on-site. Their innovative UV-catalyzed process effectively breaks down harmful PFAS molecules, making them non-toxic. With a multidisciplinary team experienced in environmental consulting, toxicology, and chemical engineering, the company is committed to creating safer communities through scalable, on-site water treatment solutions. A case on how a clinical water testing company turns scattered data into a centralized, automated, and report-ready system.
The Client
The client is a women-owned STEM company that provides a water treatment solution to permanently remove toxic PFAS contaminants and turn them into harmless byproducts on-site. Their innovative UV-catalyzed process effectively breaks down harmful PFAS molecules, making them non-toxic. With a multidisciplinary team experienced in environmental consulting, toxicology, and chemical engineering, the company is committed to creating safer communities through scalable, on-site water treatment solutions.
Challenges Faced by the Client
The client collects water samples from across organizations for clinical testing, focusing on parameters like fluoride content and other chemical markers. Eurofins Lab and Merit Lab test and analyze these samples and provide the raw lab data. However, the way the team handles this data is the challenge:
- Lack of Structured Data: Analysts receive lab files through emails from Eurofins and Merit Labs. Such data is unstructured and lacks consistency, making it hard to assign results to specific samples and chemical requirements. With no standardized format, it becomes challenging to interpret the data effectively.
- Complexity in Data Handling: The testing process involves multiple variables – same clients may submit several samples, multiple clients may send in samples, and a single sample could undergo more than 3 tests. This diversity and the lack of structure make it hard to manage and organize the results for each clinical parameter (such as fluoride data) across different tests and sources.
- Manual Data Entry for Chemical Requirements: Raw data is received in various formats (e.g., CSV, Excel, etc.) and needs to be manually standardized and consolidated into a unified structure. This data is then transformed and stored in Dataverse. Analysts manually input test results for each sample, increasing the risk of process errors.
Solutions
We addressed these challenges using Microsoft Power Platform tools to build (Power Apps), visualize (Power BI), and automate (Power Automate) reporting.
- Organizing the Data: We helped the client organize their unstructured data. Internal and external lab samples, which were initially stored as large Excel files, are now uploaded to a common SharePoint link. The tool organizes this data into seven sections: Clients, Project Details, Field Samples, Experiments, and similar clinical parameters.
- Data Visualization: We developed several Power BI reports to enhance the visualization of the testing results:
- Summary Report: An overall snapshot of key data.
- Fingerprint Graph and Fluoride Graph: These sub-graphs provide detailed insights into specific clinical parameters, such as fluoride data and test fingerprints. The graphs are designed to display accurate and real-time results, helping analysts easily interpret complex data.
- Write-Back Functionality: We implemented write-back functionality in Dataverse through Power Automate, allowing users to update and reflect changes to sample or chemical data in real time.
- Data Integration: We implemented a system to automatically collect and organize data from internal and external labs (Eurofins and Merit Labs). The data, whether in CSV or Excel files, is automatically processed and stored in a Data verse repository in a structured format. This process is known as ETL (Extraction, Transformation, Loading), which makes the data easier to work with.
Solutions Benefits
- Instant Data Sync Across the System: When a user edits or updates a Dataverse column (such as in the Client or Project sections), it is reflected in real-time across the system and Power BI graphs.
- Quickly Spot Missing or Incomplete Info: Automated workflows help users easily identify and fill any gaps, so nothing is left outdated or overlooked.
- Crystal Clear Data Visuals for Smarter Insights: With 5 Power BI reports designed for clarity, users can analyze key data points quickly and confidently without being overwhelmed.
- Always Up to Date with Real-Time Changes: When a user edits client data, sample information, or results, the changes are instantly reflected in the system and reports, giving everyone access to the latest information only.
- User-friendly onboarding with Built-In Support: To help users get started and use the system effectively, we created a Help page and a User Manual within Power BI. These resources guide users through logging into the system, interacting with the data, and understanding the reports so that they can use the system without issues.
- Savings in Manual Efforts, Time, and Cost: The solution eliminates repetitive tasks by automating data uploads, transformations, and report generation, saving the team approximately 4800 hours and $250K annually.
Conclusion
By combining Power Apps, Power BI, and Power Automate, we helped the client bring structure to unorganized lab data, automate repetitive tasks, and make insights easily accessible. What used to be a time-consuming, error-prone process is now a streamlined system that updates itself, flags missing information, and gives users clean, real-time reports they can rely on. From raw lab files to real-time dashboards, UB Technology Innovations, Inc. simplifies how clinical teams manage test data – reducing errors and speeding up insights.
