First Steps with Power Query for Microsoft Excel
Published by O'Reilly Media, Inc.
Extract, transform, and load data into spreadsheets
Analysts, researchers, and project managers building repeatable data cleaning processes on large datasets no longer need to write complex Excel formulas or programs in Visual Basic for Applications (VBA). Now they can conduct their work in Microsoft Power Query—no coding required. Power Query, an extract, transform, and load (ETL) technology, enables users to access data from structured and unstructured sources, reshape it, and load it into Excel workbooks.
Join expert George Mount to learn how to use Power Query to transform tabular data sources into tables that are ready for data analysis. By the time you’re through, you’ll be able to pivot, filter, and merge data so that it’s ready for analysis in Excel.
What you’ll learn and how you can apply it
By the end of this live online course, you’ll understand:
- How Power Query serves as an ETL tool for Microsoft Excel
- What makes data “tidy” and why tidiness is desirable for data analysis
- The role of the M programming language in Power Query
And you’ll be able to:
- Load data from Excel worksheets, workbooks, and CSV files
- Perform common data wrangling tasks such as sorting, filtering, and aggregation
- Combine data from multiple sources using joins and appends
This live event is for you because...
- You're an analyst responsible for collecting, analyzing, and interpreting data for business insights
- You frequently gather data from multiple sources to build reports in Excel
- You want to build repeatable data cleaning procedures with no code
Prerequisites
- A computer with Power Query installed (See Microsoft’s official compatibility guide.)
- A working knowledge of Excel basic tasks and functions, such as sorting and filtering, IF statements, conditional aggregates like SUMIF() and COUNTIF(), PivotTables, and VLOOKUP()
Recommended preparation:
- Take Foundations of Microsoft Excel (live online training course with Dawn Griffiths)
- Watch the “Excel Tables” and “Pivot Tables” sections of Big Data Analytics with Excel (video)
- Read “Introducing Power Query” and “Power Query Connection Types” (chapters 8 and 9 in Excel Power Pivot and Power Query For Dummies)
Recommended follow-up:
Schedule
The time frames are only estimates and may vary according to how the class is progressing.
Power Query as Excel’s ETL tool (25 minutes)
- Presentation: Why ETL for business analytics?; extracting, transforming, and loading processes; alternatives to Power Query—complex formulas, VBA, SQL, and more; Power Query and modern Excel—Power Pivot, Power Query, M, and DAX
- Hands-on exercise: Assess how to clean a messy dataset using known tools; determine what makes that dataset messy
- Q&A
What makes data “tidy”? Why does it matter? (25 minutes)
- Presentation: Spotting messy data in the wild; the principles of “tidy data”; how to reshape a dataset for ease of analysis; first steps to tidy in Power Query—loading a first data source into Power Query, inspecting it with data profiling, and beginning the data cleaning process
- Hands-on exercise: Get to tidy by determining what needs to change in example datasets
- Q&A
Break (5 minutes)
Transforming rows in Power Query (25 minutes)
- Presentation: Sorting, removing duplicates, filtering, filling, and grouping a table
- Hands-on exercise: Transform rows
- Q&A
Transforming columns in Power Query: Part I (30 minutes)
- Presentation: Changing data types, splitting columns, and reformatting text; monitoring and editing the steps of a query, loading the results, and refreshing the query
- Hands-on exercise: Clean, delimit, and transform columns of a table
- Q&A
Break (5 minutes)
Transforming columns in Power Query: Part II (30 minutes)
- Presentation: Concatenating columns, creating calculated fields, and unpivoting tables; appending several tables
- Hands-on exercise: Reshape and add calculated fields to a table
- Q&A
VLOOKUP(), meet JOIN (30 minutes)
- Presentation: The differences between left and inner joins; conducting joins in Power Query
- Hands-on exercise: Join two tables together
Wrap-up and Q&A (5 minutes)
Your Instructor
George Mount
George Mount is the founder and CEO of Stringfest Analytics, a consulting firm specializing in analytics education and upskilling. He has worked with leading bootcamps, learning platforms and practice organizations to help individuals excel at analytics.
George regularly blogs and speaks on data analysis, data education and workforce development and is the author of Advancing into Analytics: From Excel to Python and R (O'Reilly Media, 2021) and _Modern Data Analytics in Excel: Using Power Query, Power Pivot and More for Enhanced Data Analytics _(O'Reilly Media, 2024). He is a recipient of the Microsoft Most Valuable Professional (MVP) award for exceptional technical expertise and community advocacy in the field of Excel.
George holds a bachelor’s degree in economics from Hillsdale College and master’s degrees in finance and information systems from Case Western Reserve University. He resides in Cleveland, Ohio.