Theme: 10. Characterization, Fate and Transport

Topic Area: 10i. Improvements in Site Data Collection, Data Management, and Data Visualization

Formatted Title:

The Power of Well-Organized Data: Using Power Apps, Automate, and BI to Structure Information for a Large-Scale Drinking Water Sampling Effort

Background/Objectives:

An emerging contaminant was identified in drinking water wells near a manufacturing facility in the Midwestern United States. As a result, drinking water samples were collected from private wells and public water systems within certain distances of the facility. Additionally, treatment systems were offered to private wells within a specific distance of the facility.

We completed a review of publicly available well information, property records, aerial photographs, and field reconnaissance to identify approximately 465 private wells and 35 public water supplies where access would be requested to collect drinking water samples. In collaboration with our client and regulatory agencies, we structured and implemented correspondence with affected residents and public water supply owners through assigned property coordinators to explain the sampling need, obtain access for sampling, provide sampling results, and arrange treatment installation and maintenance.

Approach/Activities:

Information for each property was tracked in multiple databases so that the real-time status of each property was easily accessible for the sampling team, property coordinator, and client through a single platform: Microsoft Power BI. Microsoft Power Apps were used to track access agreements, communications with residents/tenants, sampling dates, photographs of sampling points, and treatment installation and maintenance dates. Microsoft Bookings was used to schedule sampling events and categorize them based on required analyte lists. Both Power Apps and Bookings allowed information to be accessed efficiently on an individual property basis.

Microsoft Power Automate was used to send email alerts to key staff members when action was needed for a particular property, and it was used to summarize information from Bookings alongside Power Apps information in Microsoft SharePoint. Finally, Power BI was used to compile spatial information from ESRI ArcGIS Pro, analytical data from EarthSoft EQuIS, and sampling communication information summarized in SharePoint to visualize data in interactive maps, charts, and tables. Power BI captured property status on a project-wide scale for our client and internal management. In addition, it allowed properties to be easily grouped by status for scheduling ease, such as those that needed treatment installation or those that had data ready to be shared.

Results/Lessons Learned:

This approach allowed property coordinators to provide personalized communication to over 465 private well owners throughout the entire project cycle, from explaining the sampling need to communicating the analytical results. The Power BI dashboard was used frequently by our client to track and report on project progress. It also allowed over 1,000 sampling events to date to be successfully scheduled and completed within 15 months, as well as the installation of over 85 private well treatment systems.

Menu

Sara Ramsden