Case Study: Employee Master Data Ingestion (Non-O365 Workforce)

Problem

Many factory employees do not have O365 accounts, but their basic employee information is required for Power Apps used across HR and operations. Without user profiles, these apps could not function reliably.

Users

HR teams responsible for employee data maintenance.

Scale

Initially a small HR user group, with a requirement to scale cleanly to support all factory processes.

Constraints & Considerations

• Source system: Employee data existed in Workday, but API access was not permitted

• Data access: Only CSV or Excel exports were allowed

• Security: Highly sensitive employee data required strict handling and limited distribution

• Performance: Large file uploads and row-by-row inserts could create severe performance issues if not batched

Solution

I designed a controlled ingestion process that balanced security, usability, and performance.

Key decisions included:

• Using Excel files instead of CSVs to align with HR users’ existing workflows and avoid format conversion issues that could break ingestion.

• Implementing a Power Automate flow to parse and batch upsert employee records into the backend (SharePoint or Dataverse), minimizing API calls to improve performance.

• Building a simple Power Apps interface that allowed HR users to upload files without interacting directly with the flow.

• Adding robust validation and error handling in both the flow and app to confirm file structure and provide actionable feedback when uploads failed.

Outcome

• Enabled reliable ingestion of non-O365 employee data

• Unblocked multiple downstream factory and HR applications

• While there was an initial learning curve for some users, the process was ultimately easy to adopt and maintain

What I’d Improve

• Improve flow resilience to common user errors, such as renamed worksheets, by validating data content rather than relying strictly on expected sheet names.

• This would reduce full rejections and improve the overall user experience.