Power Query Intermediate
Clean the data once, then just press Refresh.
Intermediate2 days6 modulesHands-onHRD Corp claimable
If someone on your team spends Monday morning copying exports into one sheet, deleting blank rows and fixing dates, this course is for them.
Power Query is already built into Excel. Participants learn to import from files, folders, databases and the web; combine and filter data; fix text, numbers and dates; add conditional columns; reshape tables; and load the result into worksheets, PivotTables or the Data Model. Each step is recorded, so next month’s file takes one click instead of a morning.
By the end, participants can
- Understand the importance of clean, structured data for effective analysis and reporting
- Use Power Query to clean, shape, and transform raw data quickly and efficiently
- Create and manage relationships between datasets using the Data Model
- Load and organise data into Excel tables, PivotTables or reports
Course outline
Day 1
01Power Query Fundamentals & Data Import
- Understanding the role of Power Query in modern data analytics
- Exploring the Power Query Editor and key functions
- Importing data from Excel, CSV and text files
- Connecting to databases, web sources and other external sources
02Data Consolidation, Filtering & Management
- Automating data consolidation from folders and multiple files
- Merging and appending datasets
- Filtering and sorting data
- Managing, rearranging and organising rows and columns
- Removing duplicates and unnecessary data
03Data Transformation – Text, Numbers & Dates
- Standardising, splitting, merging and extracting text
- Performing numeric calculations and rounding
- Creating calculated columns
- Transforming dates and times
- Extracting date elements and calculating periods, age and duration
Day 2
04Conditional Logic & Data Classification
- Creating index columns for sorting and segmentation
- Creating conditional columns using IF…THEN logic
- Classifying and categorising data based on conditions
- Applying transformation techniques to prepare data for analysis
05Reshaping & Combining Data
- Pivoting and unpivoting data
- Transposing tables
- Merging queries using common keys
- Appending multiple queries
- Reshaping and structuring data for analysis and reporting
06Query Management & Data Models
- Managing and optimising workbook queries
- Loading transformed data into Excel worksheets and Power Pivot
- Creating connection-only queries
- Establishing and managing data relationships
- Preparing efficient data models for analysis and reporting