Excel Power Query
Power Query is a powerful data transformation and preparation tool built into Excel that allows you to connect to various data sources, clean and reshape your data, and load it for analysis. Power Query simplifies complex data tasks through an intuitive interface, enabling you to automate repetitive data preparation steps and maintain data integrity.
Power Query can connect to databases, text files, web sources, folders, and multiple Excel files, transforming raw data into analysis-ready formats.
The course is run over 1 day and covers essential topics including connecting to data sources, transforming and cleaning data, working with columns and rows, combining queries through append and merge operations, and using advanced features like the M language and Power Pivot integration. Participants learn to create reusable queries, handle errors effectively, automate data preparation workflows, and build relationships between data sets.
Course duration: 1 day
Audience: Excel users, data analysts, and professionals who work with data regularly and want to streamline their data preparation and transformation processes
Cost: Online – R1650 (ex VAT) pp / Onsite – R1350 (ex VAT) pp
Pre-Requisites: Intermediate knowledge of Microsoft Excel, including working with formulas, tables, and basic data concepts. Participants are required to have a laptop/desktop with Microsoft Excel (2016 or later) installed.
Delivery Methods: The course can be run online or onsite, for groups of 3 or more.
Course Outline
Chapter 1 – Introduction to Power Query
1 What is Power Query
1.1 Connect to Data Sources
1.2 Transform Data
1.3 Combine Data
1.4 Load Data
1.5 Create and Load a Query
Chapter 2 – The Power Query Interface
2 The Power Query Window
2.1 Transforming the Data
2.2 Working with Columns and Rows
2.3 Understanding Data Types
2.4 Detect Data Types
Chapter 3 – Data Preview and Quality Analysis
3 Default Analytics Using Column Data Preview
3.1 Column Quality
3.2 Column Distribution
3.3 Column Profile
3.4 Understanding Data Statistics
Chapter 4 – Error Handling and Data Cleanup
4 Dealing with Errors that Occur
4.1 Step-Level Errors
4.2 Cell-Level Errors
4.3 Replacing Values
4.4 Close and Load
Chapter 5 – Data Transformation Techniques
5 Sorting Data
5.1 Transpose Data
5.2 Promoting and Demoting Headers
5.3 Using Fill
5.4 Unpivot a Column
5.5 Pivot Columns
5.6 Unpivot Multiple Column Labels
5.7 Use Extract
Chapter 6 – Connecting and Combining Data
6 Connecting to a Folder
6.1 Append and Merge Queries
6.2 Append Queries
6.3 Merge Queries
6.4 Append to Multiple Worksheets
6.5 Adding Additional Sheets
Chapter 7 – Working with Text and Columns
7 Split Columns
7.1 Split Columns by Delimiter
7.2 Split Columns by Delimiter into Rows
7.3 Grouping By
7.4 Columns from Example
Chapter 8 – Date Functions and Calculations
8 Grouping Dates
8.1 Subtracting Dates
8.2 Working with the Duration Function
8.3 Date Calculations
8.4 Adding on Days to a Date
Chapter 9 – The Date/Calendar Table
9 The Date/Calendar Table
9.1 Benefits of the Date/Calendar Table
9.2 Calculating Financial Dates/Quarters/Months
9.3 Working with Fiscal Periods
Chapter 10 – Custom Columns and Formulas
10 Write a Formula
10.1 Calculations
10.2 Perform Basic Math Operations
10.3 Conditional Columns
10.4 Create Lists
10.5 Custom Data Types
10.6 Adding a Column from the Data Types
Chapter 11 – Introduction to M Language
11 M Language
11.1 Writing a Basic Formula
11.2 The Advanced Editor
11.3 Writing a Formula using if
11.4 NumberRoundUp
11.5 TextFrom
Chapter 12 – Introduction to Power Pivot
12 Introduction to Power Pivot
12.1 Adding Data to the Model
12.2 Create a Relationship
12.3 Cardinality – the types of relationship
12.4 Writing a Formula in Power Pivot
12.5 Creating a PivotTable from multiple sources