Global Venues Corporate Training Certifications
sparktraining.eu +31 85 115 0004 Request a Quote
? Q
Home Course Finder Advanced Excel: Power Query & Power Pivots
Accounting and Finance

Advanced Excel: Power Query & Power Pivots

Microsoft Excel remains one of the most widely used tools for financial analysis, operational reporting, business intelligence, and management decision-making. However, traditional spreadsheet techniques can become inefficient when professionals work with large datasets, multiple data sources, repetitive reporting processes, and complex analytical requirements. Power Query and Power Pivot extend Excel's analytical capabilities by enabling users to automate data preparation, combine information from different systems, establish relationships between datasets, and develop advanced calculations using Data Analysis Expressions (DAX). These tools help transform fragmented spreadsheets into structured, refreshable data models that support reliable reporting and more efficient analysis. The Advanced Excel: Power Query & Power Pivots course provides participants with practical skills in data extraction, transformation, modelling, analysis, and interactive reporting. Participants learn how to connect Excel to external data sources, clean and reshape datasets, automate repetitive transformations, create relational data models, and develop powerful analytical measures. The programme also explores advanced DAX calculations, time intelligence, PivotTables, PivotCharts, data model optimisation, and dashboard development. Through hands-on exercises, participants build an integrated Excel reporting solution that reduces manual work, improves data consistency, and supports faster, evidence-based business decisions.

AF-022Accounting and Finance
Course schedules
Upcoming dates Classroom
Start date End date Location Fee Registration
Schedule information is available on request.
Request an in-house date
Online live Remote

Contact us for upcoming delivery dates and tailored in-house options.

Why Attend

Microsoft Excel remains one of the most widely used tools for financial analysis, operational reporting, business intelligence, and management decision-making. However, traditional spreadsheet techniques can become inefficient when professionals work with large datasets, multiple data sources, repetitive reporting processes, and complex analytical requirements.

Power Query and Power Pivot extend Excel's analytical capabilities by enabling users to automate data preparation, combine information from different systems, establish relationships between datasets, and develop advanced calculations using Data Analysis Expressions (DAX). These tools help transform fragmented spreadsheets into structured, refreshable data models that support reliable reporting and more efficient analysis.

The Advanced Excel: Power Query & Power Pivots course provides participants with practical skills in data extraction, transformation, modelling, analysis, and interactive reporting. Participants learn how to connect Excel to external data sources, clean and reshape datasets, automate repetitive transformations, create relational data models, and develop powerful analytical measures.

The programme also explores advanced DAX calculations, time intelligence, PivotTables, PivotCharts, data model optimisation, and dashboard development. Through hands-on exercises, participants build an integrated Excel reporting solution that reduces manual work, improves data consistency, and supports faster, evidence-based business decisions.

Course Objectives
  • Understand the roles of Power Query, Power Pivot, and the Excel Data Model in business analysis
  • Connect Excel to multiple internal and external data sources
  • Clean, transform, combine, and restructure datasets using Power Query
  • Automate repetitive data preparation and consolidation activities
  • Apply basic Power Query M language concepts to customise transformations
  • Design relational data models using Power Pivot
  • Establish table relationships and apply appropriate data modelling principles
  • Develop calculated columns and measures using DAX
  • Apply advanced DAX functions for business and financial analysis
  • Perform time intelligence calculations and comparative performance analysis
  • Create interactive PivotTables, PivotCharts, and management dashboards
  • Optimise, validate, refresh, and maintain Excel-based analytical solutions
Designed for

This program is intended for professionals involved in Accounting and Finance.

  • Financial analysts and management accountants
  • Financial planning and analysis professionals
  • Business intelligence analysts
  • Data analysts and reporting specialists
  • Finance managers and financial controllers
  • Procurement and supply chain analysts
  • Operations and performance management professionals
  • Sales and commercial analysts
  • Internal auditors and compliance reporting professionals
  • Advanced Excel users responsible for data consolidation and management reporting
Course Methodology

The programme is delivered through instructor-led demonstrations, practical Excel exercises, guided data modelling workshops, business reporting case studies, and hands-on analytical projects. Participants work with realistic datasets covering financial transactions, sales performance, procurement expenditure, operational activities, and budget information. Exercises progress from data extraction and transformation to relational modelling, DAX calculations, PivotTable analysis, and interactive dashboard development. The training emphasises practical application using Microsoft Excel with Power Query and Power Pivot functionality. Participants develop reusable data preparation workflows and reporting models that can be adapted to their organisational requirements.

Course content
Day1

Power Query Fundamentals and Advanced Data Preparation

  • Understanding the Excel business intelligence environment and the relationships between Power Query, Power Pivot, PivotTables, and the Excel Data Model
  • Navigating the Power Query Editor and understanding queries, applied steps, data types, previews, and refresh behaviour
  • Connecting to Excel workbooks, CSV files, folders, and supported database sources using appropriate connection settings
  • Cleaning and standardising datasets through filtering, replacing values, removing duplicates, handling errors, and managing missing information
  • Transforming data structures using split columns, merged columns, conditional columns, grouping, pivoting, and unpivoting
  • Practical workshop: Importing and transforming a collection of financial and operational datasets into a consistent, analysis-ready structure
Day2

Advanced Power Query, Data Integration and Transformation Automation

  • Combining multiple datasets using append queries and merge queries with appropriate join types and matching keys
  • Automating recurring file consolidation through folder-based imports, reusable transformations, and refreshable queries
  • Working with parameters, custom columns, conditional logic, and basic Power Query M expressions
  • Applying advanced transformations involving dates, text, numbers, nested data, and grouped calculations
  • Improving query reliability and performance through efficient transformation sequences, data validation, and appropriate loading options
  • Practical exercise: Developing an automated monthly reporting workflow that consolidates multiple source files and produces a validated analytical dataset
Day3

Power Pivot, Relational Data Models and DAX Fundamentals

  • Understanding Power Pivot architecture, columnar data storage, and the advantages of relational data modelling over traditional spreadsheet lookups
  • Designing fact tables and dimension tables using star schema principles and appropriate data granularity
  • Creating and managing relationships between tables, including one-to-many relationships, unique keys, and filter direction
  • Distinguishing calculated columns, explicit measures, and implicit aggregations within the Excel Data Model
  • Developing foundational DAX measures using SUM, COUNTROWS, DISTINCTCOUNT, DIVIDE, CALCULATE, and related functions
  • Hands-on workshop: Building a relational Power Pivot model and developing core financial and operational performance measures
Day4

Advanced DAX, Time Intelligence and Business Performance Analysis

  • Understanding DAX evaluation context, including row context, filter context, and context modification using CALCULATE
  • Developing advanced analytical measures using FILTER, ALL, REMOVEFILTERS, SUMX, and other relevant DAX functions
  • Creating a dedicated date table and applying time intelligence calculations for year-to-date, quarter-to-date, and prior-period analysis
  • Building measures for growth rates, percentage contribution, running totals, budget variances, and actual-versus-target comparisons
  • Troubleshooting DAX calculations and improving analytical accuracy through measure validation and relationship analysis
  • Practical workshop: Creating an advanced performance analysis model with dynamic financial KPIs, period comparisons, and management reporting measures
Day5

Interactive Dashboards, Model Optimisation and Automated Reporting

  • Creating advanced PivotTables and PivotCharts connected to Power Pivot measures and relational data models
  • Designing interactive reporting interfaces using slicers, timelines, conditional formatting, and coordinated dashboard elements
  • Applying dashboard design principles to communicate trends, financial variances, operational performance, and key management insights
  • Optimising Power Query workflows and Power Pivot models through appropriate data types, reduced model complexity, efficient calculations, and controlled refresh processes
  • Establishing data validation, refresh procedures, documentation, access considerations, and maintenance practices for reusable Excel reporting solutions
  • Capstone exercise: Developing and presenting an Integrated Excel Business Intelligence Dashboard using Power Query for automated data preparation, Power Pivot for relational modelling, DAX for advanced calculations, and interactive visualisations for management decision-making
The certificate

SPARK Training Certificate of Completion is awarded to participants who attend and complete the full training course.

Need help

Still Have Questions?

We can help with group enrollment, custom delivery formats, or selecting the right leadership pathway for your organization.

Contact Us
Explore this topic

Related Training Courses