fbpx
Go back to Courses

Power Query Academy

  • On Demand
Author: Matt Allington Miguel Escobar Ken Puls
Power Query is the most important data analysis tool since PivotTables. It allows you to clean, reshape, and combine your data with ease, no matter where it comes from. This course will teach you how to use Power Query to master your data, saving you hours of time and manual effort.
Course

Power Query Academy

$350 USD for the first year, $99 USD/year after that
This Course contains:
16h 30m of video content
16 Content Modules
15 Quizzes
6 Workshop Labs
Downloadable files and Handouts
Support and Discussion groups
Certificate of completion
DURATION:
16h 30m
TOOLS:
Excel
Power BI
SKILLS:
Data Preparation

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, SharePoint, Exchange, JSON, and even PDF files
A variety of transformation techniques and patterns 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 as a Power Query solution
Building calendar tables for dimensional models using Power Query
In-depth understanding of Power Query and the M language
Using and creating Parameters and Functions for your custom scenarios with the M language to build portable file solutions
Tips to optimize existing queries and future proof new queries

Course Overview

Course Description

Until now, building Business Intelligence solutions has been a painful, repetitive process requiring a great deal of manual effort to clean, filter, and format data before it can even be used. And updates have been tedious and error prone.

Learning to use Power Query means less time spent manipulating data and more time analyzing it to gain powerful insights and make better decisions.

Power Query will save you hours of time gathering and preparing your data before loading it into your data model.

How much time? Great question…

Consider this scenario: a regulation changes in your industry, and every company is forced to hire a new full-time employee in order to process the data that now needs to be reported to the government on a monthly basis. Every company, that is, except yours. Why? Because you were able to automate the data processing and reporting using your amazing Power Query skills, reducing the job to 15 minutes per week.

This isn’t a hypothetical scenario, this really happened for one of our clients. An average of 160 person-hours was reduced to a single hour, using Power Query.  How amazing is that? Where their competition all ended up adding a full-time employee to their corporate overhead, our client didn’t need to. That means they’ve been able to get a competitive advantage over their competitors based on what they’ve learned from our material.

Or how about the client who was able to take the rent-arrears report which took a full day of manual effort to create every single week? What would you say if you could take a job like that, and reduce it to the click of a single button that now only takes a couple of minutes? Would you like to save 52 person days of manual effort per year? Of course you would! And yes, that truly happened.

Now let’s be fair. It would be irresponsible of us to promise you that you’ll save 52 person-days per year, or a full time employee on an annual basis by learning our techniques. Not everyone will realize those kinds of productivity gains. 

But, what we can say is that we routinely get emails from people telling us that they’ve saved 4-5 hours of manual labour per week based on the things they’ve learned from our material. Think about that for a second… What if you saved only 3 hours per week? Multiply that by your hourly rate, and by 50 working weeks per year. It’s a lot of money, isn’t it? And what if you could use that time to learn even more about Power Query and automate other processes as well?

There’s one more thing that we really want you to think about here. Do you enjoy cleaning up the same data on a weekly or monthly basis? Is that satisfying for you? Wouldn’t you be more interested in analyzing that data and drawing conclusions from that data? You know – the job you were actually hired for?

Let us help you!

Our courses are 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. Let us help you get the data-cleaning out of the way, so that you can focus on the satisfying part of the job: providing the insights that you were actually hired to deliver.

This course starts at the beginning, and is intended for people who have never used Power Query at all, those who are self taught and want to ensure they haven’t missed anything, or those who just want great coverage of all of Power Query’s core features.

You’ll learn basic transformation techniques and methods to clean up a variety of common data challenges, as well as how to pull data from various data sources and pull them together into a single refreshable solution that will save you hours of time on a monthly basis.

From there, you’ll learn how to unwind difficult data patterns, dive deep into the underlying M language, and complement user-interface-driven actions with a sprinkling of code to build amazing and practical solutions. Power Query formulas, custom functions and even pro tips for future proofing solutions and improving performance; you’ll pick up the skills to make you a data ninja.

Simply put: you won’t find a more comprehensive Power Query course anywhere! Plus, everything your learn in applicable to both Excel and Power BI.

Who this course is for?

  • Anyone that needs 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 who wants total control of their data

Bonus Gifts with this Course

A digital copy of 'M is for Data Monkey' (current 1st edition and new 2nd edition when it is published)

Lifetime access to our introductory Power Query Fundamentals course

Our handy Power Query Recipes collection, including all new and updated cards for as long as you are an Academy member

Course Update Log

First release of the course as the complete Power Query Academy (course was formerly two separate courses: Power Query Essentials and Power Query Advanced).

New lessons added to the course:

  • Formulas and Conditional Logic Module
    • Real World Example of Previous Row Logic
  • Parameters and Custom Functions Module
    • Combining Multiple Web Pages
  • Date and Time Techniques Module
    • Power BI Calendar Tables and Some Tricks
  • Bringing it All Together Module
    • Flatten Absence Entries: A Tricky Example
    • Real World Extracting Web Data
 
Updates made to the Power Query Recipes:
  • Added new card to Section 20: Reshaping Data
    • Remove Top x (Dynamic) Rows (20.150)
  • Updated cards in Section 30: Combining Data
    • 30.105 to 30.116 – link added on each card that goes to the Creating a Staging Query recipe (0.110)
    • Cartesian Products (30.120) – recipe steps updated to reflect a new and improved technique
  • Updated links throughout the collection to the associated Power Query Academy videos
    • The original Power Query Essentials and Advanced courses have been merged into a single Power Query Academy course

Course Formats

On Demand
This is an on-demand course, delivered via pre-recorded video modules that you can consume at your own pace. It includes all required sample files, practice sets, and Q&A tests to validate your knowledge.

Course Content

Welcome to Power Query Academy
1 Lesson
Getting Started
Simple Transformation Techniques
1 Quiz | 12 Lessons
Unpivoting Columns
Preview
Pivoting Columns
Splitting Columns into Other Columns
Splitting Columns into Rows
Preview
Filtering Data
Preview
Sorting Data
Transforming and Adding Columns Based on Existing Columns
Adding a Column Based on a Cell Value
Working with Random Numbers
Grouping and Summarizing Data
Converting a Flat Table into Dimensions
Ranking Data
Module Quiz – Simple Transformation Techniques
Appending Data
1 Quiz | 7 Lessons
Appending Tables Individually
Appending Tables in the Current Workbook
Appending Worksheets in an External Workbook
Preview
Combine all Flat Files in a Folder
Combine a Named Worksheet from Multiple Workbooks
Combine All Worksheets in Multiple Workbooks
Loading the Latest Version of a Workbook
Module Quiz – Appending Data
Merging Data
1 Quiz | 9 Lessons
Merging Basics
Join Types
Cartesian Products
Approximate Match
Fuzzy Matching Basics
Fuzzy Matching in Data Quality
Changing Data and Adding Content
Cleanse Multiple Characters in Data
Preview
Merging for Data Auditing
Module Quiz – Merging Data
Web Based Data Sources
1 Quiz | 6 Lessons
Importing Web Data with Table Tags
Files Publicly Hosted on the Web
Reading HTML Documents
Basic Web Scraping
Advanced Web Scraping
Table Inference from Web
Module Quiz – Web Based Data Sources
Miscellaneous Data Sources
1 Quiz | 7 Lessons
Getting Data from Exchange
SharePoint Data: Introduction
SharePoint Data: Lists
SharePoint Data: All Files From Site
SharePoint Data: From Folder Experience
Importing Data from a JSON File
Connecting to a PDF File
Module Quiz – Miscellaneous Data Sources
Data From Relational Sources
1 Quiz | 5 Lessons
Connecting an Access Database
Connecting to a SQL Server Database
Connecting to SQL Server Analysis Services
Credentials and Privacy Settings
Direct Connectivity in Power BI Desktop
Module Quiz – Data from Relational Sources
Introduction to Conditional Logic
1 Quiz | 2 Lessons
Conditional Logic Basics
Creating Columns from Examples
Module Quiz – Introduction to Conditional Logic
Practice Labs
3 Lessons
Transforming a Stockflow Report into a Data Source
Merging Data from Several Sources
Grouping Data Sets
Advanced Data Reshaping
1 Quiz | 8 Lessons
Pivoting Stacked Data
Pivoting Stacked Data Revisited
Unpivoting Subcategorized Data
Pivoting Vertical Pairs
Pivoting Horizontal Pairs
Numbering Grouped Rows
Preview
A Hacker’s Approach to Numbering Group Rows
Adding Comments to Tables
Module Quiz – Advanced Data Reshaping
Managing Queries
1 Quiz | 5 Lessons
Automating Refresh in Excel
Sharing Queries
Query Organization
Bonus Tips for Working With Queries
Repointing a Query to a Different Data Source
Module Quiz – Managing Queries
Formulas and Conditional Logic
1 Quiz | 7 Lessons
Using Power Query Formulas
Conditional Logic – Manual IF Tests
Conditional Logic – Handling Errors
Catching and Targeting Errors
Conditional Logic – Working with Multiple Conditions
Conditional Logic Against the Next or Previous Row
Real World Example of Previous Row Logic (Repeat…
Module Quiz – Formulas and Conditional Logic
M Deep Dive
1 Quiz | 7 Lessons
Power Query Values
Power Query and M Syntax
M Functions – Integrated Samples and Documentation
Current Row Context
Navigation or Drill-Down
Tokens – Identifiers, Keywords, Literals, and Punctuation
Advanced Row Context
Module Quiz – M Deep Dive
Parameters and Custom Functions
1 Quiz | 5 Lessons
Manual Creation of Custom Functions
Debugging Custom Functions
UI Assisted Parameters and Functions
Parameter Tables – Extracting a Single Value
Combining Multiple Web Pages
Module Quiz – Parameters and Custom Functions
Advanced M Techniques
1 Quiz | 5 Lessons
Dynamically Creating a List of Headers
Advanced Table Append
Managing Changes to Columns
Creating Audit Queries and Alerts
Pagination and DO/WHILE
Module Quiz – Advanced M Techniques
Date and Time Techniques
1 Quiz | 6 Lessons
What is a Calendar Table?
Basic Calendar Tables – From Start Date to…
Basic Calendar Tables – From Start Date +…
Extending Calendar Table Columns
Power BI Calendar Table and Some Tricks
Recurring Dates and OFFSET
Module Quiz – Date and Time Techniques
Query Optimization
1 Quiz | 3 Lessons
Leveraging Buffer Functions
Optimizing Power Query Settings
Reducing Development Lag
Module Quiz – Query Optimization
Bringing it All Together
4 Lessons
Correcting Incorrect Records
Toggle File Combine Between Local and SharePoint
Preview
Flatten Absence Entries: A Tricky Example
Real World Extracting Web Data
Practice Labs
3 Lessons
Unpivoting Stacked Tables with Multiple Columns
Consolidating Excel Files
Building Portable File Consolidation Solutions

How subscription works?

This course is built on an annual subscription format. Your registration comes with 12 months of access to the course materials, so you can refer back to the videos and example files whenever you like. Your subscription also gives you access to our private Discussion Forum, where you can ask questions related to the course materials, as well as how to apply those teachings to your own work. This forum is an invaluable resource that also allows students to practice their own learnings by helping others with those questions.

On your annual renewal date, your subscription will be automatically renewed with the credit card information on file, unless you cancel your subscription beforehand. You will be emailed a renewal reminder about one month before the renewal date. Please see the FAQs below for information about subscription renewals and cancellations.

About the authors

Matt Allington

Microsoft MVP
Matt Allington is one of the founding partners of Skillwave.Training, and the owner and principal consultant at Excelerator BI Pty Ltd. in Australia. He is an expert in Power BI training and has 30+ years of experience in using data to deliver business value. Matt is a Microsoft MVP that specializes in Power BI, Power Pivot, and Power Query. He brings his business and IT experience to solve general business data problems quickly and efficiently.
Miguel Escobar

Miguel Escobar

Microsoft MVP
Miguel Escobar is one of the founding partners of Skillwave.Training, and the principal of Powered Solutions, a Microsoft Gold Partner in Panama. Miguel is an Excel specialist turned Business Intelligence expert using the latest tools from Microsoft – Power BI. He is the co-author of M is for Data Monkey, a blogger, and a Youtuber of powerful Excel video Tricks. He has been recognized as a Microsoft MVP and is a Microsoft Certified Trainer (MCT).

Ken Puls

Microsoft MVP, FCPA, FCMA
Ken Puls is one of the founding partners of Skillwave.Training, and the President and Chief Training Officer of Excelguru Consulting Inc. in Canada. He is a Chartered Professional Accountant, blogger, author, and trainer with over 20 years of business and IT experience. His passion lies in exploring tools to turn data into information, and teaching others how to use them. Ken is a Microsoft MVP and a leading expert in Excel, Power Query, and Power BI.

FAQ

Is Power Query Academy only for advanced users?
Not at all. If you’ve never heard of Power Query before, our courses will get you up to speed with the most powerful tool in data transformation. And if you’ve already used Power Query, you’ll get a quick refresher on the fundamentals before learning more advanced concepts. We’ve structured the courses to take you from novice to expert. And wherever you are on your learning journey, you’ll be challenged. If you prefer, you can start with the Power Query Essentials course and then later upgrade to the Bundle option (see more details in the FAQ below about renewing, upgrading, and cancelling subscriptions).
How do I access the course materials? Are they downloadable?
The videos are streamed on-demand and may be watched as often as you like. However, they are not downloadable. The example files and handouts for each lesson are downloadable.
Where are the course videos hosted?
All the videos for the course are hosted on Vimeo. Please note that some networks block access to Vimeo, in which case the videos will not be watchable. You may want to double-check that your network allows access to Vimeo before registering in the Academy, to ensure that you will be able to see the videos.
Are quizzes required for completing the course? How do I receive my Certificate of Completion?
Yes, the quiz at the end of each module is required. Each quiz contains a set of randomized true/false and multiple-choice questions (usually 10 in total). You must get at least 80% to pass the quiz. But don’t worry, you can retake the test as many times as you like! After all the modules and lessons have been completed, you will be able to download the Certificate of Completion from the My Courses area of your Account Dashboard.
What support is included with the course? How do I ask questions?
All users with a current course subscription receive access to our private Discussion Forum, where they can ask questions related to the course materials, as well as how to apply those teachings to their own work. This forum is also intended to act as a place for students to practice their own learnings by helping others with those questions. While the focus of this forum is primarily aimed at allowing students to learn by helping each other, they are monitored by our course authors as well. For technical issues related to the site, course access, or other questions regarding your account, please get in touch through our Contact Us page.
How do I renew my subscription?
Our system will automatically renew your subscription for another year on your expiry date using the billing information on file. About a month (4 weeks) before your current expiry date, you will receive an email from us reminding you that the renewal is coming up and to confirm your billing details so your access is uninterrupted.
How do I cancel my subscription?
You may cancel your subscription at any time, and you will continue to have access to all of your course materials until your expiry date. To cancel your subscription, go to the Subscriptions of the your Account Dashboard and select the subscription to cancel. After your subscription expires, you will not longer have access to the course materials, including the downloadable 'M is for Data Monkey' book and Recipe Cards.
What payment methods do you accept? What currency is used?
We accept credit card payments through our secure online payment portal, Stripe. If you would like to discuss other payment options, please get in touch through our Contact Us page. All transactions are charged in US Dollars.
What is your refund policy for this course?
We think you will love our training; however, if you are unsatisfied with the training for any reason, we offer a 14-day 100% money-back guarantee, as long as you have completed less than 25% of the course. All we ask in return is that you tell us why you were unsatisfied with the training. We do not offer refunds if you have completed over 25% of the course and/or it is more than 14 days after the purchase date.
Can an account be shared with or transferred to another user
Logins, downloads, and other materials are for the exclusive use of the registered user only. Sharing of credentials is in violation of our site policies and may result in termination of product and/or site access without notice.

Reviews

Power Query Academy

0
(0 Review)
5
4
3
2
1

Reviews

There are no reviews yet.

Only verified customers who have purchased this product may leave a review.