Data Analysis and Manipulation using Excel, PowerQuery and PowerPivot

Module C of the CinFM


Download Course Booklet

Course Description

Learning Objectives


  • Learn the full range of approaches to analyse data within an Excel environment
  • Understand the advantages and limitations of each, and enhance one’s ability to choose the most effective and appropriate method
  • Learn how to clean and manipulate data sets using Excel functionality and advanced functions
  • Understand areas or activities where automation or further tools would be most effective
  • Develop practical skills in cleaning, manipulating, integrating and analysing data sets in Excel
  • Learn how to use PowerQuery to import, clean manipulate and analyse data
  • Understand when to use intermediate or connection only queries
  • Develop skills in using PowerQuery for more advanced applications
  • Understand benefits of limitation of PowerQuery compared to native Excel
  • Learn the use of PowerPivot to analyse data sets with PivotTables, filters, and slicers
  • Understand the meaning and interpretation of DAX measures within a filter context
  • Gain practical skills in creating reports using measures and calculated columns
  • Learn how to create and analyse relational databases using the DataModel
  • Gain an overview of advanced features of PowerPivot

Key Topics Overview


  • Filters and searching · Sorting · Advanced filters and data extraction · Dynamic sorting
  • Aggregation functions · Conditional functions · Database functions
  • Lookup, Text, Date, Information and Logical functions
  • Removing duplicates and errors · Splitting and combining text fields
  • Unique keys · Creating flat tables · Multiple data sets
  • Calculated columns for advanced queries
  • Power Query · Linking to external data sources · Cleaning data
  • Manipulating data · Transposition · Pivoting · Unpivoting · Grouped queries · Appending and consolidation of data sets
  • Using the M-language · Variable types · Date formats
  • PivotTables · Filters · Slicers
  • PowerPivot · Excel Data Model · Relational databases
  • DAX language and measures · Understanding and interpretation evaluation contexts
  • Functions and iterator functions
  • Sensitivity analysis · CUBE functions · Time-intelligence · Complex queries

 Course Agenda in Detail

Day 1: Analysis of Data Sets in Excel

AM: Core Tools and Functions for Excel Data Analysis

  • Course overview and introduction
  • Overview and interpretation of key measures of single and multi-variate data, and statistical analysis
  • Core approaches to analysing a dataset, with hands-on exercises:
    • Use of filters, data sorting and searching
    • Creating category lists, removing duplicates and combined duplicates, basic data cleaning operations
    • Use of aggregation functions, conditional functions and their variants (structurally fixed queries)
    • Using PivotTables and database functions (structurally variable queries)
    • Using filters, advanced filters and extraction
    • Creating calculated columns for analysis by dates, more complex queries, and other criteria not explicit within a data set
    • Confidence intervals, and hypothesis testing

PM: Cleaning, Manipulating and Integrating Data Sets with Excel

  • Overview of functionality and functions (native Excel) to clean and manipulate data
    • Toolbar functionality and ad hoc methods
    • Overview of using functions to manipulate data
  • Hands-on exercises
    • Data cleaning, splitting, manipulation, correction, and recombining of data sets; using functionality and functions
    • Linking multiple data sets by creating unique keys and with lookup processes
  • Overview and demo of using macros to automate repeated manual or ad hoc approaches
  • Day 1 Recap, Q&A, Discussion, Close

Day 2: Data Preparation, Analysis, and Reporting using PowerQuery

AM: Introduction and Foundation Functionality

  • Introduction; recap and follow-up of data manipulation approaches in native Excel
  • Overview of PowerQuery: key menu toolbars and icons; types of queries, reports and their placement
  • Hands-on exercises
    • Data cleaning, manipulation (e.g. splitting lists, recombining fields)
    • Pivoting and unpivoting
    • Reporting as full lists and grouped queries
    • Use of calculated columns; comparison with Excel-formulae
    • Creation of Excel reports and connection-only queries

PM: Further Applications and Introduction to PowerPivot

  • Hands-on exercises
    • Appending data sets (e.g. for on-going flexible reporting, consolidation of data from business units, geographies or time-periods, …)
    • Use of data sources outside the current workbook (e.g. csv files, websites, combining data from subfolders)
    • Further work with M-language and advanced calculations
  • Comparison of native Excel versus PowerQuery; benefits and most appropriate applicability of each
  • Introduction to PowerPivot
  • Overview of the Excel Data Model and relational databases
  • Hands-on exercises
    • Creation and format of PivotTables using standard (implicit) measures
    • Use of filters, slicers, and drill-downs
  • Day 2 Recap, Q&A, Discussion, Close

Day 3: Using PowerPivot for Data Analysis; Introduction to Power BI Desktop

AM: Introduction to PowerPivot and Its Key Features

  • Introduction and recap of PivotTables
  • Introduction to explicit DAX measures and calculations; use of calculated columns
  • Hands-on exercises
    • Calculated columns
    • Writing single-column measure formula, based on single tables of data (incl. benefits compared to native Excel and PowerQuery)
    • Using inputs from multiple columns of the same table; iterator functions
    • Measures requiring filters and conditional calculations
  • Linking tables with relationships
  • Measures that vary with selected slicer categories, floating tables and sensitivity analysis
  • Hands-on exercises:
    • Measures requiring data from multiple related tables
    • Further tips to get the most from core PivotTable analysis

PM: More Advanced Reports; Introduction to Desktop Power BI and Visualisation

  • Hands-on exercises:
    • Use of CUBE functions
    • Creation of date tables and use in time-intelligence functions
    • Introduction to other possibilities and advanced measures
  • Introduction to Power BI and Visualisation
  • Hands-on exercises:
    • Creation of reports and graphics with Power BI
  • Day 3 Recap, Q&A, Discussion, Course Close