Power Automate with Power BI, Plus Error Handling and Governance

💡 Key concepts

• Power BI connector (key operations):
  • Trigger: When a data driven alert is triggered (from dashboard tile alerts in Module 2.7)
  • Action: Refresh a dataset (semantic model). Chain it after a source file lands so the report updates right away.
  • Action: Run a query against a dataset. Executes a DAX query and returns rows, e.g., to email a top-10 list.
  • Action: Export to File for Power BI Reports / Paginated Reports (needs Premium/Fabric capacity)
• Power BI → Power Automate visual: A button inside a report that runs a flow with the user's current filter context (e.g., "Email this program's status to me").
• Try/Catch pattern: Scope: Try → Scope: Catch (configure run after = has failed / timed out) → log the error to a SharePoint list + notify the owner. Scope: Finally for cleanup.
• Retry policies on actions that call services.
• Governance: Build flows inside Solutions (for moving between environments), use a service account or shared ownership (so flows don't die when someone leaves), add a co-owner, and document trigger, purpose, owner, and data touched.
• Never hard-code passwords or keys in flows. Use connection references and environment variables.

🛠 Hands-on exercise: CPI Alert → Teams + Log

Data: the Power BI dashboard tile alert from Module 2.7 (Portfolio CPI Cum < 0.95). SharePoint list Alert Log (AlertTime, Metric, Value, Threshold, Program/Scope, NotifiedTo, Acknowledged Y/N).

1. Flow trigger: Power BI When a data driven alert is triggered → select your alert.
2. Run a query against a dataset with a DAX query that returns programs with CPI Cum < 0.95:

   EVALUATE
   FILTER (
       ADDCOLUMNS ( VALUES ( dim_Program[ProgramName] ), "CPI", [CPI Cum], "EAC Gap", [EAC Gap] ),
       NOT ISBLANK ( [CPI] ) && [CPI] < 0.95
   )
   

3. Create an HTML table from the results and post to a Teams channel and email.
4. Log each alert to Alert Log.
5. Wrap the steps in Try/Catch scopes. Force a failure (wrong dataset) and confirm the catch path logs and notifies you.

📦 Mini-project: Data-Lands-Then-Refresh Chain

When a new file lands in the status_drops SharePoint library → wait for the file to be fully uploaded (e.g., a short Delay or a check that the file size is stable) → Refresh a dataset → wait/poll until refresh completes (or use a scheduled check) → Run a query against a dataset for late milestones → email the PM a "new data processed" summary. Document the flow (purpose, owner, connections, failure handling) in a one-page README.