Excel Power Query Training Overview
This Excel Power Query training course teaches attendees how to set up Excel for automation and use Power Query and Power Pivot to create pivot tables, reports, and other visualizations. Participants learn how to use the new advanced features of Excel and extract different types of data, clean up the data, and load the clean data into Excel. 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, some courses are available as live, instructor-led training from one of our partners.
Objectives
- Extract and transform data from a variety of sources using Power Query
- Clean and shape Power Query data
- Merge and append Power Query data from multiple sources
- Create formulas in Power Query to calculate new values or transform existing data
- Use Power Query data to create pivot tables, reports, and other visualizations
Prerequisites
This course is best suited for intermediate to advanced Excel users who regularly receive data sets that they must clean up and create reports in Excel.
Outline
Expand All | Collapse All
Introduction
Advanced Excel
- Set up Excel for Automation
- Excel Tips and Tricks
- New Advanced Excel Features
Power Query
- Extracting and Transforming Data
- Working with Power Query Data
Editing & Refreshing Queries
- Editing Queries
- Refreshing the Data
Appending & Merging Data
- Appending Files
- Hiding Queries
- Combining Data from Folder
- Merging Files
Creating Formulas in Power Query
- Using Custom Column Button
- Using Standard Functions
- Renaming Steps
- Editing Formulas
Using Power Query Data
- Creating Pivot Tables
- Sharing Files with Queries
Using Power Pivot
- Introduction to Power Pivot
- Loading Data into the Data Model
- Creating Relationships
- Using Related Data in Pivot Tables
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 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
Free Excel Power Query Resources
See our guide for clear, step-by-step instructions on creating a query in Excel Power Query.
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.
