Excel Power Query Training Overview
This Comprehensive Excel Power Query training course takes attendees from Power Query fundamentals to advanced data manipulation skills.
First, participants learn how to set up Excel for automation, import data from diverse sources, clean and transform data, and build stunning pivot tables and reports. Then attendees master cutting-edge techniques for complex data transformations, craft custom functions, automate tasks with M code, and optimize workflows for efficiency and accuracy.
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
- Master complex data transformations and manipulations
- Craft advanced formulas to unlock deeper insights
- Leverage powerful functions and custom parameters
- Extract and transform data from any source, including PDFs
- Use M code for data control
- Optimize workflows for efficiency and accuracy
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
Review of Power Query Concepts
- Using the QAT in Power Query
- Extracting and Transforming Data
- Using Split, Merge Columns, Extract and Columns from Example
- Using Append, Merge, and Import from Folder
- Using Grouping Options
- Organizing and Managing Steps
Power Query Options
- Setting Query Options
- Data Source Settings
- Using View Options
Using Power Query Advanced Features
- Creating and Using Parameters
- Using Convert to List
- Using Enter Data
Extracting & Transforming More Complex Data
- Extracting and Transforming PDF Files
- Extracting from Online Sources
- Transforming Complex Data
Creating Advanced Formulas in Power Query
- Creating IF statements
- Using Date Functions
- Using Other Functions
Understanding M Code
- Viewing M Code
- Editing M Code
- Writing M Code
- Common M Code Functions
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
