Stroke Audit Data Integrator

I have three Excel-based stroke-audit reports that land in our network each month. Some of the files share identical column layouts while others arrive with extra or differently named fields, yet they all describe the same underlying patient journey. I need a single, plug-and-play workbook in Microsoft 365 that can:

• Ingest any of the three source spreadsheets through Power Query, harmonise the mismatched column names and structures, and load everything into one clean “MetaData” table.
• Preserve refresh-ability so that dropping updated files into a folder and clicking Refresh updates the MetaData sheet without further manipulation.
• Expose that consolidated table so Power BI can connect to it directly- but dont need the power bi build.

In an ideal state we be able to flag an event/case for escalation to the medical director or quality team for review and input. While the primary trigger will be missing critical fields, I want the rule set to remain configurable so threshold breaches or other anomaly logic can be added later without rewriting the queries.

When a row is flagged, an automated notification (email or Teams message—whichever is simpler within the Microsoft 365 stack) should be sent to our reviewer. The message needs to include a link back to the workbook or a lightweight form so the reviewer can complete the missing data. but the reviewer should only be able to see events assigned to them to protect pHI. Once saved, the status in the MetaData table must update to show the case is closed.

Please deliver:
• The Excel 365 workbook with all Power Query steps, a clearly labelled MetaData table, and sample connectors for Power BI.
• An accompanying Power Automate (VBA cannot be used since we are in excel 365) flow that issues the escalation notification and writes the reviewer’s input back to the MetaData table.
• Brief, step-by-step instructions so another analyst can maintain the mapping rules or add new flag conditions.

If you have questions on the column mapping or notification method, let me know and we can screen-share a sample file set.

I will share our templated document with column mapping once the job is assigned. To support the build if needed rather than granted access to the platform I would need elbow support for implementation and initial launch only.