Excel & Power BI Mastery with Copilot, Power Query & Data Modeling

Course Overview

This intensive 10-day program is designed to empower professionals with advanced Excel skills, AI-powered productivity through Copilot, and end-to-end data analytics proficiency using Power Query, Power Pivot, and Power BI. Whether you’re streamlining daily tasks, automating reports, or building enterprise-grade dashboards, this course equips you with the tools and techniques to transform raw data into powerful insights.

Over 20 hours of expert-led instruction, participants will explore:

  • AI-enhanced Excel functions and automation using Copilot
  • Smart data transformation with Power Query
  • Scalable data modeling using Power Pivot
  • Interactive dashboard creation in Power BI
  • Real-world exercises for hands-on learning
  • Final assessment and Certificate of Completion

Learning Goals

To equip participants with advanced Excel techniques, AI-powered productivity (Copilot), and complete data visualization skills using Power BI, Power Query, and Power Pivot for real-world data analysis and reporting.

Training Outlines

Day 1: Excel Foundation & Copilot for Productivity

Objectives:

  • Learn interface & key Excel commands
  • Understand how Copilot in Excel accelerates work

Topics Covered:

  • Excel layout, ribbon, sheets
  • Cell referencing & basic formatting
  • Tables, ranges, filters
  • Intro to Copilot: features & interface
  • Use Copilot to:
    1. Generate formulas
    2. Summarize data
    3. Create charts

Exercise:

  • Summarize a dataset using Copilot suggestions

Day 2: Advanced Excel Formulas & Functions

Objectives:

  • Learn powerful Excel functions for business logic
  • Apply dynamic arrays & Copilot suggestions

Topics Covered:

  • Logical: IF, IFS, AND, OR
  • Lookup: VLOOKUP, XLOOKUP, INDEX-MATCH
  • Dynamic Array functions: UNIQUE, SORT, FILTER
  • Nesting functions for analysis
  • Using Copilot to optimize formula generation

Exercise:

  • Automate a sales report using formulas & Copilot

Day 3: Data Preparation using Power Query in Excel

Objectives:

  • Learn to clean and transform data using Power Query

Topics Covered:

  • Power Query Interface
  • Importing data from Excel, Web, CSV
  • Remove duplicates, change data types
  • Transform: pivot, unpivot, merge, split
  • Refreshable queries and source updates

Exercise:

  • Clean a messy HR dataset with Power Query

Day 4: Power Pivot & Data Modeling in Excel

Objectives:

  • Build data models & relationships for scalable reporting

Topics Covered:

  • Loading data to Data Model
  • Relationships (1-to-many, cardinality)
  • Introduction to DAX
  • Basic Measures: SUMX, AVERAGE, CALCULATE
  • Date tables and time intelligence

Exercise:

  • Create a model linking sales & customer data

Day 5: Excel Dashboard Design

Objectives:

  • Build interactive dashboards with visuals

Topics Covered:

  • Dashboard structure & layout
  • Pivot tables and pivot charts
  • Slicers, timelines, linked charts
  • Conditional formatting for KPIs
  • Use Copilot to summarize dashboard metrics

Exercise:

  • Create an interactive sales dashboard

Day 6: Introduction to Power BI

Objectives:

  • Get hands-on with Power BI Desktop & Service

Topics Covered:

  • Power BI architecture & flow
  • Load Excel data into Power BI
  • Understanding Power BI interface (visuals, data, model)
  • Create basic visuals: bar, column, card

Exercise:

  • Load Excel data & build a mini report

Day 7: Data Transformation in Power BI using Power Query

Objectives:

  • Clean & merge data sources in Power BI

Topics Covered:

  • Power Query Editor in Power BI
  • Append vs Merge queries
  • Creating custom columns
  • Grouping, indexing, filtering data
  • Error tracking & refresh

Exercise:

  • Merge 3 different Excel files and transform for reporting

Day 8: Power BI Modeling & DAX for Business Metrics

Objectives:

  • Create strong data models & advanced metrics using DAX

Topics Covered:

  • Relationships between tables
  • Calculated columns vs Measures
  • Time intelligence: YTD, MTD, YoY growth
  • DAX functions: RELATED, DIVIDE, SWITCH, RANKX

Exercise:

  • Build a performance scorecard with KPIs

Day 9: Building Interactive Dashboards in Power BI

Objectives:

  • Create professional, shareable dashboards

Topics Covered:

  • Designing visuals with purpose
  • Slicers, drill-downs, bookmarks
  • Tooltips, hierarchies, conditional formatting
  • Power BI themes and best practices
  • Publish to Power BI Service

Exercise:

  • Build & publish a regional sales dashboard

Day 10: Assessment, Sharing & Certification

Objectives:

  • Assess knowledge, learn about report sharing & complete the program

Topics Covered:

  • Power BI workspace: datasets, dashboards
  • Row-level security basics
  • Licensing overview

Final Assessment:

  • 1 Hour MCQ + Scenario-Based Case Task

Wrap-Up:

  • Feedback Collection
  • Certificate Distribution

Training Details

Duration: 10 Days | 2 Hours/Day | Total: 20 Hours

Trainers: 2 Experts | Mode: Live + Video Access

Includes: Assessment + Certificate of Completion

Deliverables

  • Training videos of all 10 sessions
  • 1-hour final assessment
  • Digital Certificate of Completion
  • Post-training Q&A support (7 days)

For Queries:

Contact No: +977-9801898004 (WhatsApp)
Email: services@frontline.com.np
URL: www.frontline.com.np