# Module 3: Pivot Tables and Power Query

MODULE OVERVIEW

This module focuses on using Pivot Tables and Power Query in Microsoft Excel to
summarise, transform and analyse large datasets.



Learners will explore how to create customised Pivot Tables, apply interactive
filters, work with calculated fields and measures, and import data from
different sources.



The module also covers data merging, appending, cleaning and shaping techniques,
helping participants prepare structured datasets and streamline business
reporting processes.






WHAT WILL BE COVERED


3.1 CREATING AND CUSTOMIZING PIVOT TABLES

 * Understanding the purpose and applications of Pivot Tables.

 * Creating Pivot Tables from structured datasets.

 * Customising Pivot Table layouts and fields.

 * Summarising large datasets for meaningful analysis.

 * Organising information to compare business performance across categories.


3.2 USING SLICERS AND TIMELINES WITH PIVOT TABLES

 * Understanding slicers and timelines in Pivot Tables.

 * Adding slicers for interactive data filtering.

 * Using timelines to analyse date-based information.

 * Connecting filters to Pivot Tables.

 * Creating interactive reports for different business periods and categories.


3.3 CALCULATED FIELDS AND MEASURES

 * Understanding calculated fields and measures in Pivot Tables.

 * Creating calculated fields for additional analysis.

 * Performing advanced calculations within Pivot Tables.

 * Using calculated values to evaluate business performance.

 * Extending reports with customised metrics.


3.4 POWER QUERY INTRODUCTION AND DATA IMPORT

 * Understanding Power Query and its role in data preparation.

 * Importing data from different sources into Excel.

 * Connecting to external datasets.

 * Exploring the Power Query interface and available transformation options.

 * Preparing imported data for analysis and reporting.


3.5 TRANSFORMING DATA WITH POWER QUERY

 * Understanding data transformation techniques.

 * Cleaning and organising imported datasets.

 * Merging datasets from different sources.

 * Appending multiple datasets into a single table.

 * Applying transformation steps to prepare data for reporting.


3.6 MERGING AND APPENDING DATA

 * Understanding the difference between merging and appending datasets.

 * Combining related tables using merge operations.

 * Adding records from multiple datasets using append operations.

 * Consolidating information from different sources.

 * Preparing unified datasets for business analysis.


3.7 DATA CLEANING AND SHAPING TECHNIQUES

 * Identifying and handling inconsistencies in datasets.

 * Removing unnecessary information and unwanted records.

 * Structuring data for analysis.

 * Applying data-cleaning techniques using Power Query.

 * Preparing accurate and organised datasets for reporting.


SKILLS YOU WILL GAIN

 * Creating and customising Pivot Tables for data summarisation.

 * Applying slicers and timelines for interactive filtering.

 * Performing calculations using calculated fields and measures.

 * Importing data from multiple sources using Power Query.

 * Transforming and organising datasets.

 * Merging and appending data from different sources.

 * Cleaning and shaping datasets for accurate analysis.

 * Preparing structured data for business reporting.


LEARNING OUTCOMES

By the end of this module, learners will be able to:

 * Build customised Pivot Tables to summarise large business datasets and
   identify key performance trends.

 * Use slicers and timelines to create interactive reports for analysing
   business performance across different periods and categories.

 * Apply calculated fields and measures to evaluate business metrics and support
   performance analysis.

 * Import and transform data from multiple sources using Power Query for
   consolidated reporting.

 * Merge and append datasets to combine business information from different
   departments or systems.

 * Apply data-cleaning techniques to improve data accuracy, consistency and
   reliability.

 * Streamline data preparation and reporting processes, reducing repetitive
   manual work.


CERTIFICATION ALIGNMENT

This module develops data summarisation, transformation and analysis skills that
support the Microsoft Excel certification pathway. The concepts covered help
learners work with large datasets, prepare structured reports and apply Excel
tools to practical business analysis tasks.


CONTINUE TO THE NEXT MODULE

Continue to Module 4: Advanced Data Analysis to learn how to apply statistical
functions, perform regression analysis, use Goal Seek and Solver, and conduct
what-if analysis for business planning and decision-making.