This intensive course develops practical advanced skills in business intelligence and process automation using Microsoft Power BI and Power Automate. Participants prepare and model data, develop advanced DAX calculations, design interactive reports, and configure secure publishing and refresh.

The course also covers advanced cloud flows, application integration, error handling, desktop automation, and solution management. An integrated project connects Power BI insights with automated business actions.

The five-day format prioritizes core skills and applied exercises. Specialized topics—including custom connectors, unattended desktop automation, and deployment pipelines—are covered through focused demonstrations and implementation guidance.

Duration 5 Days – 35 hrs.

Objectives

• Prepare and transform data from multiple sources using Power Query.
• Design efficient semantic models with appropriate relationships and dimensional structures.
• Develop advanced DAX measures for time intelligence and business performance analysis.
• Create interactive, accessible reports and identify performance issues.
• Configure publishing, sharing, refresh, and row-level security.
• Build cloud flows using advanced expressions, control logic, and reusable components.
• Integrate business applications and apply API integration concepts.
• Implement error handling, monitoring, and safeguards against duplicate processing.
• Build desktop flows for common repetitive tasks.
• Apply solution management, security, and deployment practices.
• Connect Power BI reports to automated business actions.

 

Target Audience 

• Business intelligence professionals and report developers.
• Data analysts and business analysts.
• Finance, operations, and reporting specialists.
• Business process owners and improvement professionals.
• Power Platform developers and automation specialists.
• IT professionals supporting analytics and workflow solutions.
• Experienced Excel users with foundational Power BI and Power Automate skills.

 

Prerequisites

• Working knowledge of Excel tables, formulas, and basic data analysis.
• Experience creating basic reports in Power BI Desktop.
• Basic understanding of data relationships and introductory DAX.
• Experience creating simple Power Automate cloud flows.
• Familiarity with Microsoft 365 applications and business workflows.
• Basic awareness of relational databases; SQL knowledge is helpful but not required.
• Training environment: A Windows computer with Power BI Desktop and Power Automate for desktop installed, plus an organizational account with access to Power BI service and Power Automate. Required permissions, connections, and licenses for the selected exercises must be configured before training.

 

Course Outline 

Day 1: Advanced Data Preparation, Modeling, and DAX

Module 1: Advanced Power Query and Data Preparation

• Connecting to Excel, CSV, databases, and SharePoint.
• Data profiling, data types, missing values, duplicates, and errors.
• Merging, appending, pivoting, and unpivoting.
• Parameters, reusable queries, and introductory M expressions.
• Query folding and efficient data loading.

 Module 2: Effective Semantic Modeling

• Fact tables, dimension tables, and star schemas.
• Relationship cardinality and filter direction.
• Active and inactive relationships.
• Date tables and business hierarchies.
• Storage mode selection and model size reduction.

 Module 3: Advanced DAX Foundations

• Measures versus calculated columns and tables.
• Row context, filter context, and context transition.
• CALCULATE and filter modification functions.
• Iterators, variables, and virtual table concepts.
• Safe division and troubleshooting unexpected totals.

Guided exercise: Prepare business data, build a star schema, and create core analytical measures.

  

Day 2: Business Analysis, Report Design, and Secure Publishing

Module 4: Time Intelligence and Business Analysis

• Date table requirements.
• Year-to-date, quarter-to-date, and month-to-date calculations.
• Prior-period comparisons and growth.
• Rolling calculations, ranking, and contribution analysis.
• Actual-versus-target and variance measures.

 Module 5: Interactive Reporting and Performance

• Selecting visuals and creating consistent layouts.
• Drill-through, tooltips, bookmarks, and navigation.
• Field parameters and dynamic measure selection.
• Conditional formatting, accessibility, and mobile layouts.
• Performance Analyzer and common report, DAX, and model improvements.

 Module 6: Publishing, Refresh, and Security

• Publishing reports and semantic models.
• Workspace roles, sharing, and Power BI apps.
• Gateways, credentials, and scheduled refresh.
• Static and dynamic row-level security.
• Validating security roles and access.
• Overview of incremental refresh, governance, and deployment pipelines.

Guided exercise: Build and publish an interactive report, configure security, and review performance.

  

Day 3: Advanced Cloud Flows and Application Integration

Module 7: Advanced Flow Architecture

• Automated, instant, and scheduled flows.
• Trigger conditions and recursive trigger prevention.
• Conditions, switches, loops, and parallel branches.
• Variables, scopes, and reusable child flows.
• Concurrency and duplicate processing prevention.

 Module 8: Expressions and Data Transformation

• Text, numbers, dates, and time zones.
• Null values and optional fields.
• Arrays, objects, and nested properties.
• Parse JSON, Select, Filter array, and Compose.
• Dynamic messages and structured outputs.

 Module 9: Business Application and API Integration

• Integrating SharePoint, Outlook, Teams, and Dataverse.
• Sequential and parallel approval patterns.
• Approval outcomes, reminders, and escalation.
• HTTP requests, responses, and authentication concepts.
• Custom connector overview.
• Pagination, filtering, and throttling considerations.

Guided exercise: Build an approval workflow with data transformation and business application integration.

 

 Day 4: Reliable Automation, Desktop Flows, and Solution Management

Module 10: Error Handling and Optimization

• Run-after configuration and structured error handling.
• Retry policies, timeouts, and controlled termination.
• Capturing errors and sending operational notifications.
• Diagnosing failures through run history.
• Improving efficiency and managing request consumption.

 Module 11: Desktop Automation

• Variables, subflows, and reusable flow structure.
• Excel, file, and folder automation.
• Browser and desktop application automation.
• Reliable selectors, synchronization, and recovery.
• Demonstration of cloud-to-desktop orchestration.
• Attended and unattended execution considerations.

 Module 12: Solution Management and Governance

• Solution-aware cloud flows.
• Connection references and environment variables.
• Moving solutions between environments.
• Managed and unmanaged solution concepts.
• Ownership, access, data policies, and sensitive information.
• Operational documentation and maintenance.

Guided exercise: Add recovery logic to a cloud flow, build a desktop automation, and prepare a flow for deployment.

  

Day 5: Power BI and Power Automate Integration

Module 13: Connecting Analytics to Business Actions

• Adding the Power Automate visual to Power BI reports.
• Passing selected report data to flows.
• Configuring flow access for report users.
• Initiating notifications and approvals from report context.
• Requesting semantic model refresh.
• Understanding refresh completion and report data visibility.

 Module 14: Integrated Solution Workshop

• Define a business reporting and automation scenario.
• Apply business measures and workflow rules.
• Connect a prepared semantic model and report to a cloud flow.
• Trigger a business action from selected report data.
• Record workflow outcomes for subsequent reporting.
• Handle failures and prevent duplicate actions.
• Validate permissions and prepare deployment documentation.

 Module 15: Assessment and Operational Handover

• Demonstrate the integrated solution.
• Review calculation accuracy and workflow behavior.
• Confirm access, monitoring, and operational ownership.
• Identify improvements and next steps.

Final deliverable: A working Power BI report connected to a Power Automate workflow, supported by basic operational documentation.