Course Catalog  > Microsoft  > Microsoft Office  > Microsoft Excel
TP3547

Comprehensive Excel Power Query Training

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.

Request Information
Request On-Site or Customized Course Info
Course Details

Duration

3 days

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.

Skills Gained

  • 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
Course Outline
  • 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