Power Query Essentials

Power Query Training · October 1, 2019

What you’ll learn

  • Understand when and why to use Power Query for data preparation tasks
  • How to connect to data sources like databases, webpages (using web scraping), SharePoint, Exchange, JSON, and even PDF files
  • A variety of Simple Transformation Techniques to solve various data challenges
  • Different methods to append (stack) data tables whether they are within the same file or spread across multiple source files
  • 7 different ways to merge tables horizontally to identify exact matches and mis-matches
  • Methods for merging tables with approximate or "fuzzy" matches
  • How to use Conditional Logic an a Power Query solution

Course Overview

Power Query is by far the best Data Preparation tool ever created for the business user. This course contains essential concepts, tips, tricks and suggestions to build upon the skills taught in our free “Power Query Fundamentals” course.

No matter how ugly your data, it needs to be cleaned for analysis, and that is the goal of this course.  You’ll learn:

  • A variety of simple transformation techniques that takes your data from ugly to tabular, all of which are refresh-able with a click.
  • How to Append (or stack) two or more tables together, whether they live in a single file, come from two different data sources, are spread across multiple worksheets in the same workbook, or spread across multiple files or workbooks.
  • How to merge data in multiple ways to identify items that either match, or don’t have a match at all.
  • How to merge data based on VLOOKUP()’s “approximate match” methodology, as well as “Fuzzy” matching, where data to be matched is misspelled or needs to be translated from one word to another
  • Where to use Conditional Logic to extract items into new columns based on certain criteria

We don’t care how ugly your data is, or where it lives.  As long as you can get access to it, we’re convinced that you can clean it up and import it using Power Query.  And that’s why this course also showcases how to connect to a wide variety of data source including databases, web pages, Microsoft Exchange, Microsoft SharePoint, PDF files and more.

Built from a practical point of view, showcasing real-world examples and techniques that you can use right away,  you’ll learn the steps, formulas, and tricks from world-class experts that will save you hours of time on a weekly basis. 

Who this course is for:
  • People who already have the skills provided in our free “Power Query Fundamentals” course (or equivalent experience)
  • Anyone that need to import and clean data to be analyzed
  • Anyone who copies and pastes data into the same Excel spreadsheet on a regular basis in order to be able to continue their workflow
  • Anyone who has ever tried to consolidate data in Excel using VLOOKUP() or other methods
  • Every user of Excel or Power BI

What you get with the course

6.5+ hours of video content

Downloadable files and handouts

7 Quizzes

'M is for Data Monkey' Digital Book

3 Workshop Labs

Premium Content & Instructors

Support & discussions group

Course Certificate of Completion

Power Query Recipes

What our students say about the course

Course update log

First release of the course as Power Query Essentials

The instructors

Ken Puls, FCPA, FCMA

Is a Chartered Professional Accountant, blogger, author and trainer with over 20 years of business experience. His passion lies in exploring tools to turn data into information, and teaching others how to benefit from them. He has held the Microsoft MVP distinction since 2006, and has been recognized as a Fellow of his accounting organization.

Miguel Escobar

Is an Excel specialist turned into BI specialist using the latest tools from Microsoft for BI – Power BI. He is the co-author of ‘M is for Data Monkey’, blogger and also Youtuber of powerful Excel video Tricks. He has been recognized as a Microsoft MVP, MCP – MCSA: BI Reporting and a Microsoft Certified Trainer (MCT).

About Instructor

+540 enrolled
Not Enrolled

Course Includes

  • 9 Modules
  • 42 Lessons
  • 7 Quizzes
  • Course Certificate