Excel Power Query Training Overview
This Excel Power Query training teaches attendees how to easily clean, transform, and load data into Excel. Participants learn how to import data from various sources and then clean and transform that data, including removing duplicates, removing blank rows, and converting text to numbers. This class is appropriate for Excel 2016, 2019, Online, and later.
Location and Pricing
Accelebrate offers instructor-led enterprise training for groups of 3 or more online or at your site. Most Accelebrate classes can be flexibly scheduled for your group, including delivery in half-day segments across a week or set of weeks. To receive a customized proposal and price quote for private corporate training on-site or online, please contact us.
In addition, we offer some courses as live, instructor-led online classes for individuals.
Objectives
- Create queries from an Excel tab, a database, and a folder
- Shape data
- Merge and append data
- Create calculations
- Group and summarize data
- Use the advanced editor
- Use Power Query Data in Excel
Prerequisites
All attendees must have prior knowledge of Excel.
Outline
Expand All | Collapse All
Introduction to Power Query
- What is Power Query?
- Setting Up Power Query
- Why use Power Query?
Demo Power Query
- Setup QAT and demo Data-Get & Transform
- Discuss Power Query Screen
- Refreshing Queries
- Editing Queries
Creating a Query from an Excel tab
- Extracting Excel Table Data (when already opened)
- Extracting Excel Named Range
- Exercise
Creating a Query from a Database
- Extracting from Access database
- Extracting from other databases
Creating a Query from a Folder
- Extracting Files from a Folder
- Adding more Files to a Folder
Transforming (Shaping) Data
- Data Types
- Date Issues
- Replacing Data
- Transforming Data
- Sorting Data
- Moving Data & Deleting Data
- Filtering Data
- Splitting & Merging Data
Using Merge and Append
- Append all tables in a spreadsheet
- Append a new table into existing query
- Merging Tables (NO MORE VLOOKUP!)
Unpivoting Data
- Demo Pivoted Data
- Unpivot Data
Creating Calculating Fields
- Create New Calculated Column
- Create If-Then-Else
- Create an Autonumbering Column
- Creating Other Calculations
- Exercise
Grouping and Summarizing
- Grouping Data
- Summarizing Data
Other
- Using the Advanced Editor (Power Query Formula Language)
- Add Column from Examples (e.g., Flashfill)
- Convert to List (deletes all other columns but selected)
- Using the View tab
Using Power Query Data in Excel
- Creating Pivot Tables
- Using the Data Model
Conclusion
Training Materials
All Excel Power Query training attendees receive a course workbook.
Software Requirements
- Excel installed from Microsoft Office Professional Plus or Microsoft 365
- Related lab files that Accelebrate will provide
How do I use Power Query?
First, you will need to import your data into Excel. The Power Query Editor can then clean, transform, and load your data. The Power Query Editor is a graphical interface that makes it easy to edit your data, including:
- Removing duplicates
- Removing blank rows
- Converting text to numbers
- Merging tables
- Grouping data
- Calculating summary statistics
What are the benefits of using Power Query?
- Power Query can pull data from a variety of sources
- It has a variety of functions that allow you to clean and transform your data
- Power Query is not difficult to use
- It makes task automation easy
Free Excel Power Query Resources
Looking for tips and tricks at a glance? Check out our Excel Power Query Cheat Sheet.
Webinar: How to use Power Query with Microsoft Excel and Power BI
Get a thorough overview of Power Query and learn how to use Power Query within Excel and Power BI Desktop to save you time and money.
Webinar: Introduction to Excel Power Query
Get a thorough overview of Power Query and watch a walkthrough of how to use Power Query within Excel.
