City of St. Catharines Learning & Development

To take this course participants must have knowledge of Excel Course 2B and Excel Course 5 topics. You must understand how to set up a valid database, the Table feature, and basic PivotTables.

If you're constantly updating spreadsheets or battling slow files packed with lookup formulas, it's time to let Power Query do the heavy lifting. This course shows you how to connect to your source data, shape it to fit your needs, and create smart, streamlined reports like PivotTables—without all the hassle. You’ll also learn best practices for structuring data so it’s Power BI-ready, giving you a strong foundation for building interactive dashboards and reports in Power BI later.

Getting Data

• Connect to Excel or Other Data
• Load Queries into Excel / Edit Queries
• Refresh / Refresh Options
• Reference / Duplicate Queries
• Merge Datasets / Use Group By
• Update Names (File, Sheet, Table, Column)

Basic Column Transformations

• Transform vs Add Column
• Remove, Duplicate, Format
• Change Case / Work with Dates
• Extract, Merge, Split / Fill & Replace Values
• Basic Calculations / Conditional Columns

Other Features

• Working with Steps / Remove Duplicates
• Data Statistics / Find & Fix Errors
• Data Types / Sort & Filter