loader image
Training Courses

Advanced Excel: Power Query & Power Pivots

Course Code
FA66
Category
Finance And Accounting
Course ID
2557
Course Outline

Course Overview

This DIXONTECH course empowers professionals to master Power Query and Power Pivot — two of Excel’s most powerful tools for data management, transformation, and advanced reporting. Participants will learn to automate data cleaning, integrate multiple data sources, create complex data models, and build insightful dashboards. The course bridges Excel and Business Intelligence (BI), enabling users to turn raw data into structured, interactive, and meaningful insights for decision-making.

TRAINING TOPICS

  • Understanding Power Query and data transformation
  • Automating data cleaning and consolidation
  • Introduction to Power Pivot and data modeling
  • Relationships, hierarchies, and calculated fields
  • DAX functions for analytics and reporting
  • Integrating Power Query and Power Pivot with Power BI

BY THE END OF THIS TRAINING COURSE, DELEGATES WILL BE ABLE TO:

Use Power Query and Power Pivot to automate and analyze complex data.

  • Clean, merge, and transform raw datasets efficiently
  • Build data models with multiple relationships
  • Write DAX measures for business insights
  • Create dashboards with PivotTables and charts
  • Automate recurring reporting tasks
  • Connect Excel to live databases and BI tools
  • Deliver professional analytics for business impact

TRAINING IS TAILORED TO

Professionals who manage, analyze, and report large data sets in Excel.

  • Financial and Business Analysts
  • Data Analysts and Reporting Officers
  • Finance and Budget Managers
  • MIS and BI Professionals
  • Auditors and Controllers
  • Operations and Planning Executives
  • Excel Power Users and Consultants

TRAINING METHODOLOGY

The DIXONTECH methodology blends practical exercises, guided projects, and case-based analysis. Participants will work hands-on with real datasets, building Power Query transformations and Power Pivot models that replicate real business reporting workflows and analytics dashboards.

DAY 1

INTRODUCTION TO POWER QUERY AND DATA TRANSFORMATION

  • Overview of self-service data tools in Excel
  • Getting started with Power Query Editor
  • Importing and connecting to data sources
  • Cleaning and transforming messy data efficiently
  • Removing duplicates, blanks, and formatting issues
  • Combining multiple tables and worksheets
  • Practical lab: automating data cleanup steps

DAY 2

ADVANCED DATA SHAPING AND AUTOMATION WITH POWER QUERY

  • M language fundamentals and applied steps
  • Conditional columns and custom transformations
  • Merging, appending, and grouping data
  • Handling large datasets and performance tuning
  • Parameterizing queries for automation
  • Dynamic folder and file imports
  • Workshop: automating monthly financial consolidation

DAY 3

POWER PIVOT ESSENTIALS AND DATA MODELING

  • Introduction to Power Pivot and its architecture
  • Creating data models and relationships
  • Understanding primary keys and hierarchies
  • Creating calculated columns and measures
  • Filtering and summarizing using PivotTables
  • Building analytical models with Power Pivot
  • Hands-on: developing a financial performance model

DAY 4

DAX LANGUAGE FOR ANALYSIS AND INSIGHTS

  • Introduction to DAX (Data Analysis Expressions)
  • Calculated fields vs. measures in Power Pivot
  • Common DAX functions: SUMX, CALCULATE, FILTER
  • Time intelligence functions: YTD, MTD, QTD
  • Context and evaluation in DAX formulas
  • KPI creation and business performance metrics
  • Exercise: writing DAX formulas for profitability

DAY 5

INTEGRATING POWER QUERY, POWER PIVOT, AND POWER BI

  • Combining Power Query and Power Pivot for automation
  • Connecting Excel models to Power BI
  • Building interactive dashboards and slicers
  • Managing refreshable datasets and queries
  • Exporting dashboards and publishing reports
  • Best practices in model design and data governance
  • Capstone project: complete automated BI workflow in Excel

 

Course Schedule

Discounts and Registration

Group & Corporate Discounts:
Available for companies enrolling multiple participants to help maximize ROI.
Individual Discounts:
Offered to self-sponsored participants who pay in full and upfront.
Registration Process:
Corporate nominations must go through the client’s HR or Training department.
Self-nominations must be prepaid via the “payment by self” option.
Confirmation:
All registrations are subject to DIXONTECH’s approval and seat availability.
Refunds:
Provided in case of course cancellation or no seat availability.
Tax Responsibility:
Clients are responsible for any local taxes in their country.