Prerequisites
Delegates should have a solid understanding of Microsoft Excel, including proficiency in using formulas and functions, sorting and filtering data, and converting lists into Excel tables.
Course Objectives
- Understand the Power Query Interface
- Importing Data and Connecting from various sources
- Transform and Combine (merge) the data in Power Query
- Add calculated columns
- Load the data into Excel
- Using the query data to perform reports
Course Content
Introduction and Objectives Introduction to Power Query
- What is Power Query?
- Overview of the Power Query Editor (Interface)
- The four phases (Connect, Transform, Combine and Load)
- Basic concepts
- What are Applied steps?
Connecting to data sources
- Importing data (e.g., CSV/Text, Excel)
- Power Query interface
- Refreshing query
-
Load and use Data Query
- Load the data into Excel worksheet
- Creating a Connection only
- Create a Pivot Table
- Refreshing the query
Transforming and combining the data
- Combining data tables (Merge and Append)
- Column operations (e.g., Rename, Move, Delete, Duplicate)
- Row operations (e.g., Deleting blank and unnecessary rows)
- Filtering, sorting, splitting, replacing values, changing data types, remove blank rows, changing case
- Remove and replace first row
- Transposing and Unpivoting Data
- Add a calculated column
- Using the group by (Basic/Advanced)
- Fill up/down feature (show PQE Script)
English
The Netherlands