An engineering consulting firm needed to upgrade wiring and monitoring systems across three U.S. gas plants. Right away, they hit a major roadblock: all input/output (IO) data lived in one massive, clunky Excel spreadsheet. It was stored on a shared drive and passed between engineers, managers, and site staff.
This setup slowed everything down. It was error-prone, hard to manage, and required several people—and days—to generate reports. Every update meant manually distributing the file again, which led to version control issues and hurt data integrity.
Clearly, the system had reached its limit. The organization needed something better—something faster, more scalable, and accessible in real time. Engineers and on-site staff had to be able to update and report on data without relying on a single server.
That’s when the transformation began.
The Challenge: Breaking Free from Excel
The Excel-based system worked for a while, but it quickly hit its limits. Data was scattered across one massive spreadsheet on a shared drive, making it hard to find information or generate reports. As the process slowed down, it became clear they needed a better solution. They needed something more robust and accessible—one that let users filter data, build reports fast, and work remotely.
The goal was clear: to create a centralized database that could integrate seamlessly with Power BI, providing a user-friendly interface for staff and engineers, and reducing the time and manpower required to access and analyze data.
The Solution: A Move to Dataverse
The team knew the first step was to select the right database platform. After evaluating three options—Microsoft Access, SQL Server, and Microsoft Dataverse—it was clear that Dataverse offered the most benefits for this project.
Why Dataverse? Here’s why it stood out:
- Cloud-Based Accessibility: Being cloud-based, Dataverse allowed remote access to data making it perfect for on-site engineers and staff who needed real-time information.
- Integration with Power BI: Dataverse integrates smoothly with Microsoft’s Power Platform, including Power BI, which was crucial for the organization’s need to generate dashboards and reports.
- User-Friendly Interface: Dataverse’s low-code design meant that even non-technical staff could navigate and manipulate the data easily, reducing reliance on IT support.
With Dataverse chosen, the team started by cleaning and organizing the existing data, setting up tables, and creating relationships within the database structure the information more effectively.
Getting Data into Dataverse
Once the system was in place, the next challenge was ensuring the data could be easily updated and maintained. The organization needed several methods for data entry and updates:
- Direct Updates in Dataverse: Users could make changes directly within the Dataverse platform.
- Mass Updates via Cloud Data Flows: For large-scale changes, load sheets were used to ensure data integrity before it was uploaded to Dataverse.
- Excel Plug-In: Since the team was still familiar with Excel, a plug-in was developed that allowed users to update data directly within Excel, which would then sync with Dataverse in real-time.
Self-Serve Manual Data Entry
Beyond Excel updates, the team needed a simpler way to enter data—one that worked on browsers and mobile devices, even outside the company network. To meet this need, MetaFactor built a Power App designed to eliminate bottlenecks and give on-site engineers the ability to update data in real time.
By removing the middleman, the app gave head office staff instant visibility, speeding up decisions and improving operations. This real-time access was a key benefit of choosing Dataverse as the backend.
The app fit easily into existing workflows. Its cloud-based design offered a major advantage: it didn’t rely on the company’s internal network. As a result, employees could access it anytime, anywhere—whether in the field or working remotely. This flexibility kept data current and teams connected, no matter their location.
The team considered developing a mobile version to boost accessibility but chose to pause. Instead, they focused on immediate project needs—refining core features and meeting current requirements before expanding further.
The Power of Power BI
Finally, the team integrated the data with Power BI to build dynamic dashboards. These dashboards let engineers and staff filter data easily and generate reports in minutes—no more digging through endless spreadsheets.
With the system in place, the time it took to generate a report dropped from days to minutes, transforming the efficiency of the team’s operations. Users could now easily extract the necessary data, customize it to their needs, and send it to relevant parties—all within a few clicks.
Overcoming Challenges: User Adoption
One major hurdle was getting the team to adopt a new workflow. Both the organization and its clients had relied on Excel for years, so change met resistance. However, as the benefits emerged—faster data access, real-time updates, and custom reports—the team gradually embraced the new system.
The Results: A More Efficient, Scalable Solution
The transformation reshaped how the organization operated. The new system didn’t just save time—it empowered the team to analyze data in ways they couldn’t before. Using Dataverse and Power BI, the organization built complex data relationships and created reports the old system couldn’t handle.
For example, the team built a Termination Schedule dashboard that pulls data from multiple tables. This tool helps electricians plan wire terminations for new equipment. What was once a manual, time-consuming task is now automated and streamlined.
Looking Ahead: Expanding the Solution
Initially, the project focused on three gas plants, but the organization now plans to expand the system to new sites and future projects. Thanks to the scalability of Dataverse and Power BI, this solution can grow with the company, supporting more projects and adapting to evolving needs.
In Conclusion
The organization replaced Excel with Dataverse and Power BI, proving how the right technology can transform operations. What started as a challenge evolved into a dynamic system that boosts efficiency, eliminates manual tasks, and provides real-time access to critical data. Best of all, the solution scales easily, empowering the team to support future projects and stay ahead in an increasingly data-driven world.
If you’re still relying on spreadsheets for complex data management, it might be time to explore what Dataverse and Power BI can do for you. The results speak for themselves.
Contact Us
At MetaFactor, we specialize in architecting tailored solutions to challenges around data storage, organization, updates, and reporting. Ready to take data management to the next level? MetaFactor can help. Whether looking to streamline processes, implement powerful reporting tools like Power BI, or migrate to scalable solutions like Dataverse, the team has expertise to guide through transformation.
Get in touch today to learn how MetaFactor can help optimize data systems and drive efficiency in operations. Stay connected with MetaFactor through our social media channels: LinkedIn, X, and Instagram.