Explore the essentials of Power Query, its purpose, historical context, and application scenarios, along with a comparison to VBA and Excel, and an overview of its different versions.

Topics Covered Include:

• What is Power Query
• Why use Power Query
• History of Power Query
• When to use Power Query vs. VBA vs. Excel for Data Transformation
• Power Query Versions

Get familiarized with the Query Editor, learn the distinction between Transform and Add Column, manage Views and Data Types, and learn the intricacies of Renaming, Deleting, Referencing, and Duplicating queries.

Topics Covered Include:

• Power Query Editor
• Transform vs. Add Column
• View
• Data Types
• Rename and Delete
• Query Reference vs. Duplicate
• Query Dependencies (Graphic Relation of Queries)

Master various data connection methods, from tables and ranges to different file types, including Excel, Text, JSON, PDF, and collating data from folders.

Topics Covered Include:

• Table/Range
• Excel File
• Text File
• Access Database
• Webpage
• JSON File
• PDF File
• From Folder

Delve into column and row transformations, data type adjustments, promotion and demotion of headers, renaming columns, replacing values, and filling data.

Topics Covered Include:

• Column Transformation
• Row Transformation
• Data Type
• Promote/Demote
• Header
• Rename Column
• Replace Values & Fill Values

Sharpen your skills in sorting and filtering data to organize your datasets better.

Topics Covered Include:

• Sort
• Filter

Learn various text manipulations, including splitting columns, text formatting, merging, and extracting specific text data.

Topics Covered Include:

• Split Column
• Format Text Data
• Merge Column
• Extract Text Data

Sharpen your abilities to perform statistical calculations, standard numerical operations, rounding, and obtaining other numerical information.

Topics Covered Include:

• Statistics
• Standard Calculation
• Rounding
• Information

Achieve proficiency in transforming date and time data to extract meaningful insights such as age, date differences, and exact time components.

Topics Covered Include:

• Year, Month, Quarter, Week, and Day
• Age, Date Only, and Subtract Days
• Hour, Minute, and Second
• Total Years, Days, Hours, and Minutes

Acquire the know-how to handle more complex, structured data types.

Explore advanced manipulation of columns through indexing, duplication, conditional logic, and creating columns from examples.

Topics Covered Include:

• Index
• Duplicate Column
• Conditional Column (If Then Else)
• Custom Column – Conditional Logic based on Multiple Columns or Multiple Criteria
• Column from Examples

Grasp techniques for grouping data by single or multiple criteria and summarizing it to conclude.

Topics Covered Include:

• Grouping Data by Single Criteria
• Grouping Data by Multiple Criteria
• Grouping and Summarizing Statistics with Multiple Criteria

Understand how to reshape your data through transposing, unpivoting, and pivoting actions.

Topics Covered Include:

• Transpose
• Unpivot
• Pivot

Learn how to combine datasets using append and merge functions, including handling fuzzy match merges.

Topics Covered Include:

• Append Data
• Merge Data

Dive into the foundational elements of M language, structure data types, and learn to extract and handle different data components within M.

Topics Covered Include:

• Intro to M
• Building Block of M Code
• Primitive Value
• Structured Data
• Extract Structured Data & Primitive Value
• Operators

Discover the use of parameters to make your queries dynamic, influencing how data is sourced and processed.

Topics Covered Include:

• Data Source
• Filtering
• Conditional Column

Discover the use of parameters to make your queries dynamic, influencing how data is sourced and processed.

Topics Covered Include:

• Function Value
• Invoke Function
• M Functions Library
• Intellisense
• Function Parameter
• Nested Function
• List Function & Table Function

Apply your skills in a mini-project to create user-defined functions that solve real-world data transformation tasks.

Topics Covered Include:

• M Code Using UI
• M Code from Scratch

Understand best practices when loading data into Excel, choosing different options to suit your analysis needs.

Topics Covered Include:

• Close & Load
• Close & Load To

Master the methods for refreshing data, ranging from manual to automated refreshes, including VBA approaches.

Topics Covered Include:

• Manual Refresh
• Automatic Refresh

Learn to anticipate, identify, and rectify common Power Query errors to ensure the reliability of your data transformations.

Topics Covered Include:

• Data Source Error
• Expression Error
• Operation Error
• Data Type Error
• Remove Error
• Replace Error

Enhance the performance of your Power Query workflows by applying optimization techniques

Topics Covered Include:

• List Buffer
• Table Buffer

Cover the importance and methods of documenting your Power Query projects through comments, properties, and understanding query relationships.

Topics Covered Include:

• Comments
• Query Properties
• Group
• Power Query Formatter
• Extract M Code for All Queries
• Extract All Query Names via VBA
• Query Relationships