Skip to content
  • Sign In
  • Try Now
View all events
Microsoft Excel

First Steps with Power Query for Microsoft Excel

Published by O'Reilly Media, Inc.

Intermediate content levelIntermediate

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:

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 develops data analytics curriculum and training programs through his company Stringfest Analytics. He’s been featured on industry outlets such as Excel TV and the MyExcelOnline podcast through his website Georgejmount.com. His blog features content on Excel and, more generally, business analytics, training, and career development. He holds a master’s degree in information systems with a certificate of achievement in quantitative methods from Case Western Reserve University.

    linkedinXlinksearch