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

Mastering Power Query with Microsoft Excel

Published by O'Reilly Media, Inc.

Intermediate content levelIntermediate

Transform data, automate repetitive tasks, and open the door to Power BI

Course outcomes

  • Understand what Power Query is and how it can help you work more efficiently
  • Learn how to import data from a variety of data sources, including Excel workbooks, folders, and databases
  • Explore Power Query Editor to transform data using its intuitive interface instead of Excel’s built-in functions

Course description

Do you want to spend less time manipulating data in Microsoft Excel? Do you need to collect data from other workbooks or data sources? Or do you wish you could automate repetitive, manual tasks such as preparing monthly reports?

Join Excel expert Dawn Griffiths to learn how Excel’s Power Query tool can help save you hours of work by making data manipulation a breeze. You’ll find out how to perform common tasks ⎯ such as splitting columns and extracting codes⎯using Power Query’s intuitive editor. You’ll discover how to import data from elsewhere, including time-saving techniques for automatically collating data that’s been added to a single folder. You’ll discover how to add derived and custom columns, combine data sets, and work with multiple queries. By the end of the course, you’ll have all the skills you need to use Power Query in your day-to-day work, import and transform data with ease, and apply it to other products such as Power BI.

What you’ll learn and how you can apply it

  • Load data from the web, Excel files, folders, and other data sources
  • Manipulate and transform data with ease
  • Automate repetitive, manual data manipulations
  • Combine data sets and work with multiple queries
  • Use Power Query to work more productively and save hours of time

This live event is for you because...

  • You’re an Excel power user who wants to learn how Power Query can help you work more efficiently.
  • You’re a data analyst who needs to import data into Excel and manipulate it.
  • You’re a business manager who wants to find out if Power Query can help your team save time.
  • You gather data from different sources on a regular basis and use it to produce reports.
  • You spend lots of time using functions to manipulate data, and you’d like to find out how Power Query can help.
  • You want to automate manual, repetitive tasks without having to write VBA code or macros.
  • You want to learn how to use Power Query with Excel so that you can apply this knowledge to another product.

Prerequisites

  • Basic Excel knowledge, including tables
  • To take part in the exercises, you'll need an installation of Excel for Windows (Excel 2016 upwards) or Excel 365 for Mac. All demonstrations will use Excel 365 for Windows

Recommended preparation:

Recommended follow-up:

Schedule

The time frames are only estimates and may vary according to how the class is progressing.

Introducing Power Query (15 minutes)

  • Presentation: What’s Power Query?
  • Demonstration: Power Query in action; loading data from the web
  • Hands-on exercise: Use Power Query to load data from the web to Excel
  • Q&A

Using queries (15 minutes)

  • Demonstration: Loading table data into Excel; renaming columns; applying filters to restrict the data that’s imported
  • Hands-on exercises: Load data; rename a column; filter the rows
  • Q&A

Transforming data (40 minutes)

  • Demonstration: How to use data types (date, currency); splitting columns using delimiters; splitting columns by extracting codes from text; other ways of transforming data
  • Hands-on exercises: Split a column into forename and surname; split the codes into a separate column
  • Q&A
  • Break

Adding extra columns (30 minutes)

  • Demonstration: Adding derived columns; adding a column by example; adding a custom column
  • Hands-on exercises: Add a column; add a custom column
  • Q&A

Combining data sets (40 minutes)

  • Demonstration: Merging data to include extra columns; appending data to include extra rows; bulk loading data from files in a folder
  • Hands-on exercises: Merge the data; append the data
  • Q&A
  • Break

Grouping data (15 minutes)

  • Demonstration: Loading data directly to a pivot table; grouping data within Power Query
  • Hands-on exercise: Group the data
  • Q&A

Working with multiple queries (25 minutes)

  • Demonstration: Extracting steps to a new query; putting queries into groups
  • Hands-on exercise: Extract the data
  • Q&A

Your Instructor

  • Dawn Griffiths

    Dawn Griffiths is an author and trainer with over 20 years of experience using Excel. Her most recent book is Excel Cookbook, and she's also written several books in the Head First series, including Head First Statistics, Head First Android Development, and Head First Kotlin. Dawn also developed the animated video course The Agile Sketchpad with her husband, David, to teach key concepts and techniques in a way that keeps your brain active and engaged.

    Xlinksearch