×
×
The client, a leading healthcare-focused organization, had long depended on a large Excel workbook to manage critical operational data. Over time, this approach became unsustainable. As data volume and user activity increased, the Excel system suffered from slow performance, frequent crashes, and the inability to handle concurrent edits without data being overwritten. The lack of validation allowed inconsistent, junk entries, while the absence of an audit trail made it difficult to track changes or maintain data integrity. These limitations began to severely impact daily operations and decision-making.
As the client’s operations scaled, their Excel-based system began showing serious limitations across performance, usability, and data accuracy. What once worked for a small team became a bottleneck in a growing, collaborative environment
The Excel-based system lacked input controls, allowing users to freely enter data into any field. This resulted in issues like inconsistent naming conventions, incorrect values, and frequent missing entries, which severely impacted data quality and downstream analysis.
There were no constraints or detection mechanisms in place to catch duplicates during data entry. This led to the same entity being entered multiple times with slight variations, causing confusion, bloated datasets, and unreliable reporting.
Excel provided no visibility into who modified what or when. This made it impossible to trace the source of errors, resolve data conflicts, or hold users accountable, especially when the file was accessed by multiple departments.
As data volume increased, the Excel file grew cumbersome, taking longer to open and slowing down significantly during edits or filtering. It occasionally crashed or froze, forcing users to restart their work and lose progress.
Multiple users could not work on the Excel file at the same time without risking data conflicts. Concurrent edits often resulted in overwritten entries, version mismatches, or lost updates, making teamwork highly inefficient.
There were no dropdowns or formatting rules to guide user input, which led to inconsistent naming conventions for countries, product types, and other key fields. This inconsistency made data aggregation and analysis difficult and unreliable.
Bulk data entry was done through manual copy-paste from CSVs into Excel, with no validation or formatting checks. Errors like misaligned columns, incorrect delimiters, or invalid values often caused database ingestion failures and required manual correction.
To overcome these challenges, we designed a modern solution using Microsoft Power Apps with a centralized Azure SQL Database. The system was split into modular apps to boost performance and ease maintenance. Guided forms with validation controls ensured accurate data entry, while a custom bulk upload framework with row-level checks improved data quality at scale. Features like autofill logic, real-time scoring, and a secure backend further enhanced usability, consistency, and audit readiness.
To improve system performance and ensure long-term maintainability, the solution was restructured into three modular Power Apps instead of relying on a single monolithic application. Each app was designed to handle only 3–4 related tables, which reduced data load and significantly enhanced responsiveness. A unified navigation screen with deep links allowed users to move seamlessly between apps, preserving a cohesive user experience. This modular setup also enabled independent deployment of updates or bug fixes, minimizing the risk of disruption and reducing overall downtime.
To address issues with inconsistent and unvalidated data, structured guided forms were implemented in place of free-text fields. These forms used dropdown controls connected to lookup tables, ensuring users could only select valid values. The dropdowns included search functionality to improve usability, particularly when working with large datasets. Conditional formatting and real-time validation prompts guided users through the forms, helping them complete required fields accurately and reducing the likelihood of errors during submission.
A tailored bulk upload system was developed to allow users to import data efficiently without compromising integrity. Power Apps were extended to accept “!-delimited” text as a safer and more reliable alternative to standard CSV files. To support this, a custom Excel-based converter tool was created, enabling users to generate properly formatted files for upload. Within the app, the Split() function was used to parse and map each data value to the appropriate fields, streamlining the upload process while maintaining data structure.
To further strengthen data quality, each row in the bulk upload was individually validated within the app before submission. The validation checks included confirming correct data types, ensuring mandatory fields were completed, and verifying foreign key references. Any invalid rows were flagged with clear error messages indicating the specific issue and its location. This allowed users to correct only the problematic entries and re-upload, making the process more efficient and reducing the back-and-forth typically involved in bulk data correction.
The app employed two data handling approaches to ensure precise and secure updates. Existing records were edited using the Patch() function, which allowed selective updates based on unique record IDs. For new data entries, the SubmitForm() method was used, adhering to low-code best practices and simplifying form-based submissions. This separation of logic ensured that edits and new entries were handled appropriately, reducing the risk of accidental data overrides and maintaining system stability.
To improve data entry speed and maintain consistency, intelligent autofill logic was applied across the forms. Based on user selections, such as a product name or organization code, dependent fields were automatically populated using the LookUp() function. These autofilled fields were set to read-only to prevent accidental changes, further enhancing data reliability. This feature was particularly useful in populating product details, associated codes, and organization metadata with minimal manual input.
For scenarios involving risk or performance scoring, real-time calculation logic was embedded directly into the forms. Inputs such as severity, impact, and likelihood were used in simple arithmetic expressions to generate a live total score that updated dynamically as values changed. This provided users with immediate feedback, reduced errors due to manual calculations, and improved the clarity and reliability of scoring-based evaluations.
For scenarios involving risk or performance scoring, real-time calculation logic was embedded directly into the forms. Inputs such as severity, impact, and likelihood were used in simple arithmetic expressions to generate a live total score that updated dynamically as values changed. This provided users with immediate feedback, reduced errors due to manual calculations, and improved the clarity and reliability of scoring-based evaluations.
NeenOpal was selected for this project based on strong expertise in low-code development using Microsoft Power Apps and proven capabilities in building scalable backends with Azure SQL Database. With prior experience delivering high-volume, enterprise-grade solutions, our team was well-positioned to modernize the client’s Excel-based system into a streamlined, future-ready platform.
Our approach emphasized collaboration, engaging stakeholders regularly, building iterative prototypes, and adapting quickly to feedback. By combining technical precision with a focus on usability, Neenopal ensured the final solution was not only robust and scalable, but also intuitive and aligned with day-to-day operational needs.
The new system delivered measurable improvements in usability, performance, and data reliability. By moving away from Excel and adopting a modular, low-code platform, the client gained a solution that was both scalable and user-friendly:
The transition from an outdated, error-prone Excel-based system to a robust, modular Power Apps solution marked a significant operational leap for the client. By introducing structured data entry, intelligent validation, and scalable architecture, the new platform not only improved data accuracy but also helped users with a streamlined, intuitive interface. The result is a future-ready system that supports high-volume operations, reduces manual intervention, and fosters long-term data reliability across the organization.
Senior Associate Consultant
Content Writer
