microsoft partner logo color
8245  Reviews star_rate star_rate star_rate star_rate star_half

Advanced Excel Power Query

This Advanced Excel Power Query training course takes your team's data-wrangling skills to the next level. Participants learn powerful techniques for cleaning, shaping, and analyzing data from...

Read More
Course Code OFC-107
Duration 1 day
Available Formats Classroom

This Advanced Excel Power Query training course takes your team's data-wrangling skills to the next level. Participants learn powerful techniques for cleaning, shaping, and analyzing data from diverse sources. In addition, students learn how to refine their workflows with advanced querying, custom functions, and M code mastery.

Skills Gained

  • 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

It is recommended that all participants take Accelebrate's Introductory Excel Power Query course.  This course is intended for advanced Excel users.

Course Details

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

Outline

  • 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