Home » Software Training » Excel Power Query

Excel Power Query Bryan Hong

  • Level Beginner
  • 36 Lessons (3hr : 11min)
  • Audio: English

Master Microsoft Power Query and you will be able to:

- Create your own Power Query data transformation from scratch!
- Understand the essence of the Power Query cleanups, and see them in action!
- See how Power Query is used with real examples!


After this class you will learn about:

Introduction
- Excel Power Query Editor Ribbon

Transform Data
- Trim in Excel Power Query
- Format Dates and Values in Excel Power Query
- Parsing URLs in Excel Power Query
- Split Text Fields in Excel Power Query
- Group By in Excel Power Query
- Unpivoting Columns
- Pivoting Columns
- Split Columns into Other Columns
- Filtering Rows and adding a "Year" Column
- Sorting Data
- Transform vs Add Columns

From Folder
- Import From Folder in Excel Power Query
- Doing Auto Cleanup in Excel Power Query
- Extract Data from Forms in Excel Power Query

From Workbook
- Extract Multiple Criteria in Excel Power Query
- Extract Multiple Worksheets in Excel Power Query

Joins
- Intro to Joins
- Merging
- Full Outer Join
- Right Anti Join

Tips and Tricks
- Convert Reports into Pivot Tables
- Modulo

Who is it for? This course is for people who want to master Excel Power Query and speed up in working with your data.

What will I learn? You will learn how to use the Excel Power Query from scratch and will be able to transform dirty data into usable data.

Course Includes: 36 Video Lessons

Table of Contents

Section 1: Excel Power Query

Power Query Intro and Excel version 3:04
   Introduction about Power Query   
Power Query and Query Editor Ribbon 8:38
   Learn about the Power Query Ribbon on its different functions   
Trim 5:27
   A simple example of trimming text to see Power Query in action   
Format Dates and Values 2:15
   Format Dates and Values using Power Query   
Parsing URLs 5:26
   Manipulate text URLs using Power Query   
Split Text Fields 9:34
   Splitting using delimiters is easy with Power Query   
Group By 2:57
   You can also group by values using Power Query   
Import From Folder 6:33
   Power Query can get information from all files in a folder   
Doing Auto Cleanup 6:57
   We work with Power Query how it can automatically retrieve new information   
Extract Data from Forms 13:26
   Working with forms outputted from a software extract can be processed by Power Query   
Extract - Multiple Criteria 4:39
   Let us see how we can work with multiple criteria in Power Query   
Extract Multiple Worksheets 4:03
   See how to work with multiple Excel Worksheets   
Unpivoting Columns 5:19
   Changing the orientation of your data in Power Query by unpivoting it   
Pivoting Columns 2:18
   Changing the orientation of your data in Power Query by pivoting it   
Split Columns into Other Columns 4:04
   Splitting columns into multiple ones using Power Query   
Filtering Rows 5:03
   Filtering is essential in your data transformation   
Sorting Columns 2:20
   Sorting is very easy to do in Power Query   
Transform and Add Columns 6:41
   Learn the main difference between transforming columns and adding columns   
Intro to Joins 3:31
   Join multiple tables using Power Query   
Merging 7:43
   See how merging works in Power Query   
Full Outer Join 5:43
   See how this type of join is used and where it is applicable   
Right Anti Join 8:50
   See how this type of join is used and where it is applicable   
Convert Reports into Pivot Tables 5:04
   Now let us convert a report file into clean data, then into a pivot table   
Modulo 5:45
   Use the power the modulo in Power Query and see how it is useful   

Section 2: M Language in Power Query

M Introduction 2:42
   Introduction to the M Language in Power Query   
Enabling M in Power Query 2:19
   See where you can enable M   
Simple Expressions 7:35
   Learn how to use simple expressions in M   
Simple Expressions - Nested Expressions 2:54
   We take it up a notch of working with more complicated simple expressions   
Variables 8:00
   See how variables are used   
Functions 6:06
   See how we form M functions   
Functions - Reusable Functions 2:13
   M Functions can be reused to make our code cleaner   
Functions - Invoking 0:35
   See how M Functions are called   
Passing Functions 8:11
   We can pass functions for it to be used in other parts of our code   
Passing Functions - keyword each 2:41
   See how the each keyword makes our code more readable   
Finding the list of M functions 2:28
   We can get the full list of M functions inside Excel   
Using M Functions 9:59
   We can use M functions easily for our column transformations   

Master Microsoft Power Query and you will be able to:

- Create your own Power Query data transformation from scratch!
- Understand the essence of the Power Query cleanups, and see them in action!
- See how Power Query is used with real examples!


After this class you will learn about:

Introduction
- Excel Power Query Editor Ribbon

Transform Data
- Trim in Excel Power Query
- Format Dates and Values in Excel Power Query
- Parsing URLs in Excel Power Query
- Split Text Fields in Excel Power Query
- Group By in Excel Power Query
- Unpivoting Columns
- Pivoting Columns
- Split Columns into Other Columns
- Filtering Rows and adding a "Year" Column
- Sorting Data
- Transform vs Add Columns

From Folder
- Import From Folder in Excel Power Query
- Doing Auto Cleanup in Excel Power Query
- Extract Data from Forms in Excel Power Query

From Workbook
- Extract Multiple Criteria in Excel Power Query
- Extract Multiple Worksheets in Excel Power Query

Joins
- Intro to Joins
- Merging
- Full Outer Join
- Right Anti Join

Tips and Tricks
- Convert Reports into Pivot Tables
- Modulo

Bryan is a best-selling book author of the 101 Excel Series paperback books and a Microsoft Certified Systems Engineer with IT experience of more than 10 years.

Courses Bryan is focused on range from SQL, Excel Formulas, Power Query, Power BI, Excel Charts, Macros and much more. His passion lies in helping students in whatever means possible, whether it be reaching them via courses or by books.

Educator®

Please sign in to participate in this lecture discussion.

Resetting Your Password?
OR

Start Learning Now

Our free lessons will get you started (Adobe Flash® required).
Get immediate access to our entire library.

Membership Overview

  • Available 24/7. Unlimited Access to Our Entire Library.
  • Search and jump to exactly what you want to learn.
  • *Ask questions and get answers from the community and our teachers!
  • Practice questions with step-by-step solutions.
  • Download lecture slides for taking notes.
  • Track your course viewing progress.
  • Accessible anytime, anywhere with our Android and iOS apps.