Course Code: intadv
Duration: 16 hours
Prerequisites:

Target Audience: Participants with working knowledge of Excel and basic Power BI familiarity
Objective: Deepen analytical and visualization capabilities to generate actionable insights and support strategic decision-making

Course Outline:

Day 1: Advanced Excel for Analytics

1. Advanced Data Cleaning and Preparation

  • Using Power Query in Excel
  • Advanced formulas (INDEX-MATCH, TEXTJOIN, array formulas)
  • Error handling with IFERROR and data validation techniques

2. Data Modeling and Analysis Tools

  • Creating relationships between multiple data tables
  • Using Excel’s Data Model
  • Introduction to DAX functions in Excel

3. Advanced Visualization in Excel

  • Dynamic dashboards with form controls (e.g., drop-downs, checkboxes)
  • Advanced chart types (combo charts, waterfall, sparkline, gauge charts)
  • Conditional formatting for storytelling with data

Hands-on Exercises:

  • Create a dynamic Excel dashboard using embassy-style data
  • Build and relate multi-table datasets

Day 2: Power BI – Advanced Data Modeling and Analytics

4. Data Modeling Best Practices

  • Star vs Snowflake schema
  • Relationships, cardinality, and filtering behavior
  • Using calculated columns and measures

5. Advanced DAX for Business Logic

  • CALCULATE, FILTER, ALL, SWITCH
  • Time intelligence functions: YTD, MOM, QOQ
  • Scenario-based examples for deriving insights

6. Advanced Visualizations and Storytelling

  • Designing insightful reports with bookmarks and drill-through
  • Creating custom tooltips and KPI indicators
  • Using themes and templates for branding

Hands-on Exercises:

  • Build a fully-interactive Power BI report with DAX
  • Create a KPI dashboard for reporting embassy programs