Excel Power Query and Power Pivot Training Course

Duration 5 days 10 modules · 33 contact hours
Level Intermediate Certificate on completion
Who attends Business Analyst, Financial Analyst and others
Fee from USD 1,100 KSh 95,000 for Kenyan organisations
Where 52 cities + online See dates by city
Next start 9 Nov 2026 Course code SD-BI-066

Before you join: Participants should be confident Excel users who can work with worksheets, tables, formulas, filters, sorting and standard PivotTables.

Dates & prices

Two start dates a month in every city, and live online on weekdays or weekends. Fees are per participant; classroom fees include tuition, materials, lunch and refreshments, and the certificate.

Suggest a date or city

Showing the next six months. Later dates, other cities and private deliveries for your team are available on request.

Course overview

Many reporting teams still spend hours copying files, cleaning columns, matching lookup tables and rebuilding monthly PivotTables. This creates delayed management information, inconsistent calculations and workbooks that only one person can maintain. Power Query and Power Pivot turn Excel into a repeatable reporting environment by separating data preparation, data modelling and analysis.

This course teaches participants to import data from spreadsheets, folders, CSV files and SQL Server; clean and standardise records with Power Query; combine files through append and merge operations; and build a relational Excel Data Model. Participants create DAX measures for totals, ratios, variances, year-to-date figures, rolling periods and rankings. They also produce PivotTables, PivotCharts, slicers, timelines and fixed-format reports that draw from controlled measures rather than scattered worksheet formulas.

Teaching uses short instructor briefings, worked reporting cases and guided analysis of finance, operations and customer data. Participants apply methods to realistic reporting issues such as monthly actual-versus-budget reporting, supplier spend analysis and service-volume trends. Each participant leaves with a reusable Excel business intelligence workbook structure, including a documented query pipeline, data model, DAX measure set, reporting layout and refresh checklist.

The course suits analysts and reporting professionals who already use Excel confidently and need to automate recurring reporting without moving immediately to a separate BI platform.

What you will be able to do

  • Import and profile data from Excel files, CSV files, folders and SQL Server sources
  • Transform inconsistent source data using Power Query Applied Steps and M expressions
  • Append files and merge lookup tables using appropriate join types and matching keys
  • Design a star-schema Excel Data Model with facts, dimensions and relationships
  • Create DAX measures for aggregation, ratios, variances, rankings and filtered calculations
  • Build time-intelligence measures using marked date tables and DAX calendar functions
  • Produce interactive PivotTable and PivotChart reports using slicers, timelines and KPIs
  • Document refresh, reconciliation and ownership procedures for recurring Excel reports

Benefits of attending

For you

  • Replace manual monthly data preparation with repeatable query refreshes
  • Build DAX measures instead of maintaining complex copied formulas
  • Create management reports that reconcile to documented source data
  • Diagnose broken queries, relationships and calculations independently
  • Develop a portfolio-ready Excel BI reporting model

For your organisation

  • Reduce manual consolidation time for recurring management reports
  • Improve consistency of measures across finance, operations and commercial reporting
  • Create auditable data preparation steps instead of undocumented worksheet edits
  • Increase report refresh reliability when source files change each period
  • Retain reporting knowledge through documented models, measures and handover checklists

Course modules

10 modules over 5 days

Participants can explain how Power Query, Power Pivot and the Excel Data Model work together.

  • Power Query, Power Pivot and the Excel Data Model architecture
  • Workbook connections, queries, tables and model storage explained
  • Import modes for CSV, workbooks, folders, databases and web sources
  • Data types, query dependencies and refresh paths inspected systematically
  • Star schema roles: fact tables, dimensions, keys and grain
  • Choose worksheet formulas, Power Query or DAX for each task

Participants can connect to common business data sources and assess data quality before transformation.

  • Connect to CSV, Excel, SQL Server and folder-based source files
  • Set file paths, privacy levels and credentials for controlled access
  • Profile columns using quality, distribution and column-profile indicators
  • Promote headers and assign data types before downstream transformations
  • Parameterise source locations with named cells and Power Query parameters
  • Document source assumptions, refresh ownership and exception-handling requirements

Participants can clean, reshape and standardise source data through repeatable Power Query steps.

  • Filter nulls, errors and outliers using reproducible Applied Steps
  • Split, extract and replace text with Power Query transformations
  • Standardise dates, currencies and categories using locale-aware type conversion
  • Fill, transpose, pivot and unpivot columns to reshape operational data
  • Group transactions with aggregations and conditional custom columns
  • Read generated M code to diagnose transformation logic
  • Use try otherwise expressions to handle predictable source errors

Participants can consolidate recurring files and connect related tables using reliable matching methods.

  • Append monthly files into a standardised transaction table
  • Merge lookup tables using join kinds and matching keys
  • Select inner, left outer, anti and fuzzy matching techniques
  • Resolve duplicate keys before relationship creation and measure calculation
  • Build folder queries that absorb newly received source files
  • Create reference queries to separate staging from reporting transformations
  • Trace query dependencies with Query Dependencies and load settings

Participants can build an Excel Data Model with valid relationships and reporting-ready table structures.

  • Design star schemas with transaction facts and conformed dimensions
  • Set one-to-many relationships using unique keys and correct filter direction
  • Distinguish calculated columns, measures and source transformations by purpose
  • Create a marked date table for time intelligence calculations
  • Hide technical fields and organise measures in display folders
  • Detect ambiguous relationships, many-to-many risks and circular filtering
  • Use Power Pivot Diagram View to inspect model structure

Participants can write and validate DAX measures for controlled business calculations.

  • Write explicit measures using SUM, COUNTROWS, DIVIDE and CALCULATE
  • Apply filter context and row context to DAX evaluation
  • Use RELATED, RELATEDTABLE and iterators for relationship-aware calculations
  • Construct ratio, variance and contribution measures with safe denominator handling
  • Control filter behaviour with ALL, REMOVEFILTERS and KEEPFILTERS
  • Test measure results against source totals and reconciliation rules
  • Name and format measures for consistent business interpretation

Participants can create period-based measures for management reporting and trend analysis.

  • Generate calendar attributes for financial year, quarter, month and week
  • Calculate year-to-date, prior-year and rolling-period measures with DAX
  • Compare actuals, budget and forecast using variance measures
  • Use DAX SAMEPERIODLASTYEAR, DATEADD and DATESYTD functions correctly
  • Handle incomplete periods and nonstandard fiscal calendars explicitly
  • Build ranking measures with RANKX and selected-category filters
  • Validate time calculations against period-close reporting rules

Participants can produce interactive and fixed-format Excel reports from Data Model measures.

  • Build Power PivotTables from measures, dimensions and hierarchies
  • Arrange slicers, timelines and report filters for controlled exploration
  • Create PivotCharts that compare trends, composition and variance
  • Use drill-through and Show Details to inspect underlying records
  • Apply CUBE functions for fixed-format management report layouts
  • Design KPI cards with targets, status indicators and variance
  • Control field lists, number formats and descriptive report titles

Participants can maintain efficient, controlled and auditable Excel BI workbooks.

  • Configure refresh settings, credentials and connection properties safely
  • Reduce model size through column removal, aggregation and data-type selection
  • Prevent duplicate loads using query load settings and staging patterns
  • Identify refresh failures from Applied Steps, source changes and privacy settings
  • Apply workbook protection and access controls to sensitive reports
  • Maintain data dictionaries, query annotations and measure definitions
  • Establish refresh checklists, reconciliations and ownership handovers

Participants can plan an Excel BI reporting solution with clear requirements, controls and ownership.

  • Frame reporting questions as metrics, dimensions, grain and refresh requirements
  • Map source-to-report lineage from raw files through model measures
  • Prioritise measures using decision purpose, user audience and action frequency
  • Review a finance or operations reporting case for data-quality risks
  • Create an application plan with milestones, owners and validation checks
  • Define acceptance criteria for refresh, reconciliation and report usability
  • Present model assumptions, limitations and next actions to stakeholders

Exercises and examples are adapted to each cohort's sectors and roles.

What you take back to work

  1. Power Query transformation checklist
  2. Excel Data Model design template
  3. DAX measure library
  4. Time-intelligence calculation guide
  5. Management reporting layout template
  6. Refresh and reconciliation checklist

Who should attend

  • Business Analyst — automate recurring reports and produce controlled management information
  • Financial Analyst — build actual, budget, forecast and variance reporting models
  • Management Information Analyst — combine operational data sources into refreshable reports
  • Data Analyst — prepare data and create Excel-based analytical models
  • Operations Analyst — analyse service volumes, productivity and process performance
  • Reporting Manager — standardise report logic, refresh procedures and quality checks

Skills and tools covered

Power Query transformationData model designDAX measure writingTime intelligencePivotTable reportingRefresh governance

Tools and standards

Microsoft Excel Power Query, Microsoft Excel Power Pivot, DAX, Power Query M

How the course runs

Training methodology

The course combines concise instructor briefings with guided case work using realistic Excel reporting scenarios. Participants inspect source data, build query steps, model relationships, write DAX measures and test outputs against reconciliation rules. Group exercises focus on choices such as join type, table grain, date-table design and filter behaviour. Short simulations address common failures, including changed source columns, duplicate keys and incorrect totals. Participants finish by preparing an application plan for a reporting process they can improve after the course.

Requirements and prerequisites

Participants should be confident Excel users who can work with worksheets, tables, formulas, filters, sorting and standard PivotTables. Experience preparing recurring reports or analysing business data is useful. Participants should understand basic business measures such as totals, percentages, variances and dates. Prior use of Power Query, Power Pivot, DAX, SQL, databases or programming is not required. The course starts with the Excel Data Model and builds DAX from first principles, but it is not intended for beginners who still need core Excel formula and worksheet skills.

A typical training day

08:30 – 10:30First session
10:30 – 10:45Refreshment break
10:45 – 12:30Second session
12:30 – 13:30Lunch and networking
13:30 – 15:00Third session
15:00 – 15:15Refreshment break
15:15 – 16:30Workshop and daily review

Live online deliveries follow the same structure in East Africa Time, with shorter screen blocks and longer breaks.

What the fee includes

  • Instruction by a practitioner facilitator
  • Full course workbook and materials
  • Exercise files, templates and case studies
  • Refreshments and lunch on classroom deliveries
  • Certificate of completion
  • Post-course application plan

Frequently asked questions

You should be comfortable with worksheets, Excel tables, formulas, filters and standard PivotTables. You do not need prior Power Query, Power Pivot, DAX, SQL or programming experience.

Bring a laptop with a current desktop version of Microsoft Excel that includes Power Query and Power Pivot. You may also bring a non-sensitive example of a recurring report or anonymised source file to inform your application plan.

It is designed for analysts, finance professionals, reporting specialists and operational staff who prepare recurring reports from multiple data sources. It also suits managers responsible for improving report reliability and reducing manual spreadsheet work.

Advanced Excel formula courses usually focus on worksheet functions, formula construction and spreadsheet techniques. This course concentrates on importing, transforming, modelling and analysing data through Power Query, Power Pivot and DAX.

You can use the methods to automate monthly file consolidation, standardise data cleaning and create governed measures for management reports. The reporting solution planning module helps you identify a suitable process for early application.

The course provides about 32.5 contact hours across five instructor-led days, normally from 08:30 to 16:30. Time is allocated to data preparation, table combination, data modelling, DAX, reporting, refresh controls and application planning.

Questions about this course?

Ask about dates, fees, group bookings or running it for your team. Our training team replies by email.

Ask on WhatsApp info@skillsetdevelopment.com

Related courses in Business Intelligence