Power Query Academy

Authors: Ken Puls, Matt Allington, Miguel Escobar

Learn how to use Power Query to import and transform data automatically in Excel and Power BI. We’ll show you how to clean, reshape, and combine your data with ease, no matter where it comes from, and refresh it with just a click. Power Query will help you master your data, saving you hours of time and manual effort.

Duration:
27h 26m
Tools:
  • Excel
  • Power BI
Skills:
  • Extract and Import Data
  • Clean and Transform Data
  • Automate 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

Power Query Academy

$350Renews at $99 per year
(All prices in USD)
  • 27h 26m of training content
  • 24 modules
  • 15 quizzes
  • 6 workshop labs
  • Downloadable files and Handouts
  • Support and Discussion
  • Certificate of Completion

Power Query Academy

$350

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 you 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

Sample Lessons

Not sure if this course is right for you?

Visit our Learning Journey page to compare courses by skill level and area of specialization.

Bonus Gifts with this Course

A digital copy of 'Master Your Data with Excel and Power BI'

Our handy Power Query Recipes collection

Reviews

Power Query Academy

Average Rating

4.95 / 5

4.9/5
Jeff
Jeff
June 30, 2021
Read More
So many courses and programs take a more academic-style approach to teaching this stuff. That is the difference I found at Skillwave, and why I chose to invest here (and continue to renew). The reasons for a lesson are about "do you have the tools and techniques to solve the real business challenge you're facing". And it's obvious that the instructors are using their experience doing just that, as the foundation for course topics. So if you're like me in your learning needs, I can save you the time scouring the web like I did. This is your one-stop shop.
Haydee Mera Escalante
Haydee Mera Escalante
October 18, 2022
Read More
I really enjoyed learning techniques and tricks to solve problems that are frequently found when cleaning data to generate reports. In general, the course seemed very good to me. The inclusion of subtitles in the videos helps me to better understand the content, since English is not my native language. I thank you for the course, I have learned a lot in these months. I will continue to advance with other courses that I have activated on your platform.
Stanton
Stanton
June 30, 2021
Read More
Learning Power Query has made it possible to see a 1000% increase in my work productivity. Providing a client the ability to transform data that took them days to format in a useable form to virtually a mouse click was terrific. Power Query Academy provided me with the tools and understanding to do just that. The Skillwave formula of comprehensive training through video and working examples is the best way I’ve found to learn. Once again, thank you, Ken, Matt, and Miguel.
Tom Senft
Tom Senft
September 15, 2022
Read More
I liked the course structure. Explanations are easy to follow. Demos very well done, and very easy to follow. Very hands-on. Feedback support via the forum was excellent - very responsive and good advice. Gave solid information that can be applied quickly to real business problems. Great course! Thoroughly enjoyed it. We’ve already applied learnings from the course to hands-on work at our company.
Chris Aragao
Chris Aragao
November 10, 2022
Read More
I think there is a wealth of information and the trainers were excellent. I liked that there was just so much relevant content and the walkthroughs really teach you theory, then you apply it.
Doug R.
Doug R.
May 29, 2023
Read More
I liked that you could work at your own pace, it fit into my busy schedule. Course layout and instructors explain everything for the most part very well. I'm glad I took the course, it has helped me introduce this software technology to my company which is just now starting to see the importance of data, its structure and accessibility.
Rodger Moran
Rodger Moran
November 24, 2022
Read More
I love that I can trust the teachers of this course of what principles to include at this level. This course is not for the faint of heart. It starts quite basic, but it does ramp up to some heavy-duty concepts and procedures. Each lesson is explained so professionally. I love you guys!!
Harlan Friedman
Harlan Friedman
July 18, 2022
Read More
[The trainers] are each doing a great job. The lessons are so compelling that I think about them while I’m walking my dog! The progression from the simple to the complex is really helpful. To solve some of the later lessons, I relied on the skills I learned earlier.
Steffen Fischer
Steffen Fischer
November 9, 2021
Read More
Power Query Academy has changed my whole experience in transforming data from almost any shape with less or no Excel formula. Step-by-Step videos are easy to understand and follow.
Nick Burns
Nick Burns
June 29, 2021
Read More
I've been with this course through several iterations of their training website. It is by far the best resource/training for Power Query. The Advanced M Techniques module alone are worth the price of admission. I especially like that they keep adding new content as the application (Excel / Power BI) changes.
Michael H.
Michael H.
October 28, 2024
Read More
Very well structured course and the authors are just great. Keep on the great work!
C.K.
C.K.
September 15, 2024
Read More
I liked the thorough examples. I would have liked it If the problems in the examples were a bit more financed based, but I realize that is specific to my role. Keep up the great work. I thought this was very helpful.
Doug M.
Doug M.
September 11, 2025
Read More
I liked the way the lessons are laid out, very easy to follow along. Very practical lessons as well, for things that one would use every day. I knew the reputation of Ken going in, but Matt Allington is #@%&!!! BRILLIANT. Can't wait to due more with DAX and Power BI. Going in, I had a few years working with PQ and I do understand it reasonably well, however I still learned an incredible amount. I will recommend this course to anyone who really wants to learn Power Query. Excellent value for the cost of the course. Pretty sure we made up the cost before I even finished it.
S.S.
S.S.
August 8, 2024
Read More
I loved the ability to talk about a topic, see it done and then to do it in an exercise helped firm up the learning.
Claudia R.
Claudia R.
July 15, 2024
Read More
The course had good explanations and examples to practice.
Mary Ann
Mary Ann
July 1, 2024
Read More
There is so much information and great reference material. Between the recipe cards and the training manual we were able to apply several tools to our current processes that will be great time savers as we move forward.
Stephanie
Stephanie
June 28, 2024
Read More
I loved the real life examples, and practice labs to apply the information.
Lucas
Lucas
June 18, 2024
Read More
I loved the material and instructors.
Felipe Uderman
Felipe Uderman
June 17, 2024
Read More
Great course, a real academy.
Ibtissam Nkaili
Ibtissam Nkaili
May 4, 2024
Read More
The course covers the keys elements, I loved the detailed steps.
Sara P
Sara P
April 29, 2024
Read More
I loved the exercises done after the theory, but it seemed that some of the exercises are the same, changing the final steps and repeating all previous steps in each process.
Rogers Reiche de Mendonca
Rogers Reiche de Mendonca
April 2, 2024
Read More
I loved the combination of the theoretical part with the presentation of examples and practical tips. Congratulations on the comprehensiveness and quality of the training explanations.
Maria
Maria
March 22, 2024
Read More
The course helped me solve problems in a more efficient way.
Rod Apfelbeck
Rod Apfelbeck
March 18, 2024
Read More
I loved the seemingly comprehensive coverage of many data sources and how to deal with each. I appreciate how responsive you all were when I left comments about the videos.
Micheal Reynolds
Micheal Reynolds
March 8, 2024
Read More
The course has great examples to learn from. The instruction was very good. Great explanation of how to perform the steps. Thank you for putting this information together. It helps explain concepts that are very valuable.
Jorge Guida
Jorge Guida
February 19, 2024
Read More
The instructors were engaging and explained complex concepts clearly. The course structure was logical, and the resources provided were helpful. I highly recommend this course to others.
Marcelo Bastos
Marcelo Bastos
January 22, 2024
Read More
This is a very complete course. The material is quite comprehensive.
Rodrigo Moreno
Rodrigo Moreno
November 30, 2023
Read More
The course seeks to teach through practical examples, which facilitates the absorption of knowledge. Furthermore, the course presents several usage tips.
Mark Chesley
Mark Chesley
November 14, 2023
Read More
The format was very well developed and delivered. Great job everyone. I can appreciate all the "under the hood" work that goes on to develop a course like this. Years in the making. Nice to see the team expanding and the global reach.
Peter D.
Peter D.
October 27, 2023
Read More
I loved the demos and step-by-step instructions. This is a great course, jam packed with lots of great information! Great job by all! I am looking forward to taking another course soon.
Trond Brandeggen
Trond Brandeggen
July 14, 2023
Read More
I liked the comprehensive material, both videos and text. Easy to follow, and very useful recipe cards that certainly will be used in future projects. Thank you all for a good learning experience! I'll certainly both need and want to revisit some of the material.
Luiz O.
Luiz O.
May 30, 2023
Read More
This course is a masterpiece to learn Power Query.
Mark Aird
Mark Aird
May 4, 2023
Read More
I liked the detailed explanations, well-edited videos and extensive scope and depth.
Colin
Colin
May 1, 2023
Read More
I liked the comprehensive coverage of the content, logical flow, articulate explanation, and easy-to-follow demos.
Pieter
Pieter
April 25, 2023
Read More
Things I liked:
• An e-book in the same structure to quickly refer to afterwards
• Subtitles! Quite handy for non-English speakers
• Clear structure, much detail
• Training materials (downloads), exercise labs
• Mistakes made in the movies—either intentionally or unintentionally—and then immediately fixed: they show you how easy it is to recover from them (or how to analyze them) and that failure is an option, at least during development. 😉
• Video player: option to increase the speed!
THANK YOU!!! Money VERY well spent, and I've already used some of the course techniques in my solutions. I'm also trying to convince my colleagues to join 😉
John Giger
John Giger
April 19, 2023
Read More
I liked the well-structured lessons, practical tips throughout the lessons, and very understandable explanations; simply a great course. I appreciate that the lessons get an update regularly.
Edmond Dzimah
Edmond Dzimah
April 18, 2023
Read More
I liked the elaborate and practical explanations.
Melanie Lacroix
Melanie Lacroix
November 14, 2022
Read More
I love seeing the steps on video. I also enjoy your approach to teaching. Wonderful course! Thank you so much!
JB
JB
November 8, 2022
Read More
I liked the depth and breadth of coverage. Simple explanations and real-world use cases. Course materials were well-organized and easy to use.
João Teixeira
João Teixeira
October 13, 2022
Read More
Great Instructors! Very clean explanations and a very comprehensive course!
Judith Calvo
Judith Calvo
October 3, 2022
Read More
It is so well organized and easy to understand. Kudos on an awesome course!
Michael T.
Michael T.
September 26, 2022
Read More
I liked how clear each subject was and how thoroughly each topic was explained.
Bernard Clark
Bernard Clark
September 1, 2022
Read More
I liked that it was self-paced, provided tips for organisation and possible gotchas (e.g., "remove errors before nulls"), steps for alternative ways to solve problems, and an insight to M language using trial and error.
Bryan Dimick
Bryan Dimick
August 30, 2022
Read More
The course was well documented and detailed. Plus, the flow of the training was easy to follow, well designed. Luckily, I was used to the methodology having witnessed some of the videos in prior google searches. Plus, I recently worked through the M is for (Data) Monkey book. I love the recipes, as I am not always doing reports (broad responsibilities), so being able to flip back to the recipes and videos will help me when I have long gaps of being away from PQ or P-BI.
Holly D.
Holly D.
August 10, 2022
Read More
As a beginner, I loved how detailed the course was. It was a great course!
Matthew Wagner
Matthew Wagner
June 28, 2022
Read More
I loved the course! Now I am going to go back to lesson number #1 and do it again!! I liked the thorough nature by which the material was presented. Loved "going under the hood"!
Darren G., CPA
Darren G., CPA
June 5, 2022
Read More
Power Query is saving us heaps of time and enabling us to create reports that we were not previously able to do. I can’t speak highly enough of it and the Skillwave training!
Julie Meadow
Julie Meadow
May 26, 2022
Read More
I loved how many lessons there were and how they were broken down. As an auditory learner I had some trouble with the voices switching often at first.
Benjamin
Benjamin
May 20, 2022
Read More
I liked transforming dirty data to something usable from multiple sources. I use the skills acquired almost daily. Almost all of the lessons are easy to follow and understand. All instructors are really good, but Ken is great. I hope to take other courses.
Jorrit Ruigrok
Jorrit Ruigrok
May 20, 2022
Read More
I like the complete set of information, and that it is updated regularly.
Avinash
Avinash
May 17, 2022
Read More
Thank you to the course instructors for sharing their knowledge on this topic! I liked that the course showed several Power Query features through examples, reviewed important topics that users will see everyday, and then built upon them to more advanced M topics. It was a great course!
Sarah Brown
Sarah Brown
May 17, 2022
Read More
I loved the detailed examples.
Beverly Bruce
Beverly Bruce
December 21, 2021
Read More
I am transitioning into a BA role and my coworker suggested I google Ken Puls. I am so happy I did that and took this course. I would not recommend it for anyone who is not disciplined but they break it down so that anyone can understand it. This is so empowering! Thank you.
Matthew Wagner
Matthew Wagner
June 30, 2021
Read More
This course is absolutely fantastic! I love the in depth approach to all the topics contained in the syllabus. I like to watch the videos multiple times and I often pick up something new or gain a firmer understanding of the material with each view. I used to go to the guy that knew Excel for help, now he comes to me! I have other co-workers that also request my assistance. It has been quite rewarding applying my knowledge... I highly recommend this course.
Thomas
Thomas
June 30, 2021
Read More
A well structured very helpful intro course into the power of Power Query. First class training done by the best instructors and real experts. I love how it is presented. The recipes are very handy and helpful.
Matthias
Matthias
June 30, 2021
Read More
I wholeheartedly recommend Power Query Academy! Each video does not only teach a course topic but is also filled with helpful tips and gems which make it so much easier to work with Power Query. Ken, Miguel and Matt know what they do, teach it well and they continue to add regularly new content to the course. On top of this Ken, Miguel and Matt also help you in the forum with any question you might have regarding the course content and when you have problems applying your new knowledge to your own data tasks. As a bonus to the course you get also a good, not techie, book and recipe cards.
Wilfried
Wilfried
June 30, 2021
Read More
I'm very content, best course I've ever bought. Thank you.
Benjamin Barrow
Benjamin Barrow
June 30, 2021
Read More
I have learned so much from this course and love having a subscription so that I get updates as they come out. The format is great, the presentations are clear and well organized. This is some of the best training I have ever taken and I have taken a lot of training. I highly recommend Skillwave. I started learning about Power Query when Bill Jelen introduced me to M is for Data Monkey and it had not even been published yet. Sign up and subscribe! You will not regret it!
Angelique
Angelique
June 29, 2021
Read More
This is the best laid out, in depth training I’ve found for Power Query. The break downs of and progressions towards a deeper understanding are really helpful and have given me ways of being able to do just about anything I need with Power Query. Thank you Skillwave!
Sylvia Grozdeva
Sylvia Grozdeva
June 29, 2021
Read More
I am really enjoying the course as it gives you deep dive into PQ. The course is structured in user-friendly way, the lessons are delivered by great professionals and the steps are easy to apply after the detailed explanations and the showed examples. I am recommending the course with both hands as it is totally worth it.
Kajsa
Kajsa
June 28, 2021
Read More
These guys are something extra! With an outstanding patience and educational talent, they directly went beyond the "monkey do" -stuff, they rather started explaining the underlying logics and then the necessary work arounds, to achieve the results I could not manage. If you consider putting some money into your personal learning, this is the choice. They don´t give you "just a pattern" to follow, you learn why and you learn extras. Thank you for being there!
John
John
June 28, 2021
Read More
This course is for deep understanding of this new exciting and increasingly essential area for all power Excel users and analysts. The content is a clear and explained in an efficient way, the quizzes really test you and the practical examples test the leanings are thorough. You will become proficient if you do this course. Highly recommended.
Seth Barron
Seth Barron
June 28, 2021
Read More
I continue to be impressed with the value delivered by Power Query Academy, it covers a stunning amount of ground for what has become an essential daily tool for me. These guys are on-top of questions too, if you’re taking the course and have a problem you’re very likely to get a thoughtful response by the next day. Do yourself a favor and skip your next obligatory continuing education course and do this instead.
Kasper
Kasper
June 28, 2021
Read More
Great course! Really informative and great learning.
Matthieu Rancinan
Matthieu Rancinan
June 28, 2021
Read More
Great job with those videos, pretty clear and precise.
Ceren Karakurt
Ceren Karakurt
June 27, 2021
Read More
Many interesting and comprehensive topics covered. There were some stuff which I was applying without knowing the logic but now I have all the power.
Yoash
Yoash
June 20, 2021
Read More
This changed the way I use Excel, long work hours became easy and short time with excellent outcome. Very easy to understand, comprehensive videos, well done, great job.
Chris Kroll
Chris Kroll
June 17, 2021
Read More
I have completed the course Power Query Fundamentals and am 50% complete with Power Query Academy. As an a Senior Analyst/Programmer writing and implementing code for several decades, I found these courses very helpful. I like the way they describe the technical specifics of each section then follow that up immediately with a sample exercise. The labs were especially helpful in solidifying the learned information. A lot of good information there to help me down the road with Power Query. I like it!
Rochma
Rochma
June 16, 2021
Read More
I have changed my excel performance by over 100%. i have learned so much. the clarity and ease of learning with the platform is exemplary. the customer service is above and beyond!
Thomas H.
Thomas H.
June 2, 2021
Read More
The Power Query Academy course is really great, lots more tricks than I expected to see before I decided to purchase it. It is the small stuff that speeds up my workflow. It is also the systematic learning as opposed to mainly using Google to find answer to a current problem. My biggest regret is that I didn’t buy it earlier, since it could have saved me time thru the well-structured learning in the course as opposed to my random learning before that thru blogs and Google.
Allesandro
Allesandro
June 2, 2021
Read More
I have an 8 month's work experience on Power BI and DAX. In the first months I've thought of Power Query as a "complementary", nice to have skill. Not fundamental. After 8 months, having taken on more serious and difficult projects, I realize it's not a complementary skill. It's THE skill of a good BI analyst that works with Power BI. As soon as you start working on more complicated projects, this is what you realize.
Elayne Jones
Elayne Jones
June 25, 2021
Read More
The trainers are clear and concise, and the resources are invaluable! Thank you!
Ian Grove
Ian Grove
June 25, 2021
Read More
The website, first and foremost, is the best platform I have seen and reduced all the standard friction of learning sites. The content in this course and the fundamentals course are spot on, delivered in digestible chunks, and there are chances to practice. The Quizzes ARE NO JOKE, but essential to understand things well enough to move on to other areas and helpful (no gotchas!).
Mitchell
Mitchell
June 17, 2021
Read More
The Skillwave platform is the most polished of the leaning platforms I've been on. With three top-notch experts in the field, the content is bound to be awesome. The course materials are like a deep dive into their public blog content. It was gratifying having my questions answered within an hour of posting in the forums. Personally, the investment was a no-brainer. Their course material BEGINS where others end. The extra knowledge and practical skills (along with the brilliant Recipe Cards for reference) are priceless.
Abdulrahman Joseph
Abdulrahman Joseph
June 17, 2021
Read More
Power Query Academy has changed my whole experience in transforming data from almost any shape with less or no Excel formula. Step-by-step videos are easy to understand and follow. I loved the hands on examples and the recipes.
Koen de Witte
Koen de Witte
January 6, 2024
Read More
I loved the advanced M techniques & deep dives!
Previous
Next

Course Update Log

2021-03-05

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

2021-03-29

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

2021-06-28

New lessons added to the course:

  • Managing Queries Module
    • Using Relative References in Queries
  • Advanced M Techniques Module
    • Techniques to Rename Columns

2021-08-02

Release of Master Your Data with Excel and Power BI book (formerly titled M is for (Data) Monkey). Course registrants now receive the new second edition instead of the original first edition.

2021-09-28

New module added to the course called Allocating Values to Periods, which contains 10 new lessons:

  • Allocation Summary
  • Evenly To Each Day in Period
  • To Month by Days in Period
  • Evenly to Each Month in Period
  • To Year by Days in Period
  • Evenly to Each Year in Period
  • Over x Days From Start Date
  • Over x Months From Start Date
  • Over x Years From Start Date
  • Over x Instances at y Intervals From Start Date

Updates to two existing lessons in the Simple Transformation Techniques module:

  • Working with Random Numbers
  • Ranking Data

Updates made to the Power Query Recipes:

  • Added an entire new section to the recipe collection that corresponds with the new Academy module on Allocations. Section 65 – Allocations Over Periods contains the following new recipes:
    • Allocating Values Over Periods Between Two Dates (65.100)
    • Allocating Values Over x Periods From a Start Date (65.101)
    • Allocate Evenly to Each Day in Period (65.110)
    • Allocate to Month by Days in Period (65.120)
    • Allocate Evenly to each Month in Period (65.130)
    • Allocate to Year by Days in Period (65.220)
    • Allocate Evenly to each Year in Period (65.230)
    • Allocate Over x Months from Start Date (65.310)
    • Allocate Over x Years from Start Date (65.320)
    • Allocate Over x Consecutive Days from Start (65.410)
    • Allocate Over x Instances at y Intervals (65.420)
  • Added two new recipes to Section 60 – Date & Time Techniques:
    • Creating a Custom GetMonthEnds Function (60.850)
    • Creating a Custom GetYearEnds Function (60.855)
  • Updated a recipe in Section 70 – Miscellaneous:
    • Add a Random Number to all Table Rows (70.100)

2021-12-20

Power Query Academy Updates:

  • Retired two lessons from the Data from Relational Sources module:
    • Connecting to a SQL Server Database
    • Credentials and Privacy Settings.
  • The topics from the retired lessons have been rolled into a new lesson:
    • Connecting to SQL Server Databases
  • Added three new lessons to the module:
    • SQL Databases With Defined Relationships
    • SQL Databases Without Defined Relationships
    • Understanding Query Folding

Power Query Recipes Updates:

  • Added a new recipe to section 20 – Reshaping Data:
    • Pivoting Vertical Sets (Recipe 20.130)
  • Updated existing recipes:
    • Pivoting Stacked Data (Recipe 20.120) – name of the “Integer-Division” column updated to reflect new wording (was formerly “Integer-Divide”)
    • Allocate over x Years from Start Date (Recipe 65.320) – the formula logic for the first period was corrected
    • Random Numbers (Recipe 70.100) – note added that the recipe is now much easier with versions of Power Query released after October 2021

2022-03-31

Power Query Academy Updates:

  • Most of the existing lesson videos and handouts updated to use the Skillwave Training branding (many still had the original Power Query Training branding)
  • Updated Cartesian Products lesson (in the Merging Data module)
    • Includes a new step to Remove the [MergeKey] column to reflect the updated Power Query UI
  • Updated Approximate Match lesson (in the Merging Data module)
    • Fixed an incorrect reference on Recipe slide in video
  • Updated Grouping Data Sets practice lab
    • Now references a new Azure database (original database no longer available)
  • Added a new lesson in the the Data from Relational Sources module:
    • Parameterized Queries in SQL Server

Power Query Recipes Updates:

  • Added a new recipe to section 00 – Best Practice Architectures:
    • Quickly Setting Data Type of All Columns (Recipe 0.550)
  • Added a new recipe to section 70 – Miscellaneous:
    • Choosing Columns vs Removing Columns (Recipe 70.130)
  • Updated existing recipe in section 30 – Combining Data:
    • Cartesian Products (Recipe 30.120)

2022-06-27

Power Query Academy Updates:

  • Updated the Fuzzy Matching Basics lesson (in the Merging Data module)
    • All new video and example files
  • Updated the Sharing Queries lesson (in the Managing Queries module)
    • All new video and example files
  • Updated the Pagination and DO/WHILE lesson (in the Advanced M Techniques module)
    • All new video and example files
  • Have begun adding English captions to videos, starting with modules at the end of the course. Captions have so far been added to the 3 updated lessons mentioned above, as well as to all lessons in the following modules:
    • Date & Time Techniques
    • Allocating Values to Periods
    • Query Optimization
    • Bringing it All Together

Power Query Recipe Update:

  • Added new recipe to section 20 – Reshaping Data:
    • Shift Non-Aligned Columns Left (Recipe 20.160)

2022-09-28

Power Query Academy Updates:

  • Added a new module called Dataflows (Power Query Online), which contains 8 lessons:
    • Background to Dataflows
    • Introduction to Dataflows
    • Demo: Creating a Dataflow
    • Demo: Using a Dataflow
    • Computed Tables
    • Power Query and Dataflows
    • Taking control of a Dataflow
    • Dataflows Summary
  • Completed the English captions for all videos in the course, including those in the new module.

Power Query Recipes Update:

  • Added a new recipe to section 20 – Reshaping Data:
    • Unfill in Power Query (Recipe 20.170.2209)

2022-12-28

Added all content from our free Power Query Fundamentals course to the beginning of the Academy. The modules added are:

  • Introduction to Power Query (5 lessons)
  • Importing Basic Data (4 lessons)
  • Query Settings (6 lessons)

Updated the module called Web Based Data Sources. The lessons contained in this module are:

  • Basic “From Web” Concepts (NEW)
  • Web Hosted Files (UPDATED, formerly named Files Publicly Hosted on the Web)
  • Web Pages with Table Tags (UPDATED, formerly named Importing Web Data with Table Tags)
  • Inferred Tables (UPDATED, formerly named Table Inference from Web)
  • Tables from Example (NEW)
  • NOTE: quiz questions for this module have been updated where needed to reflect the content

Added a new module called Advanced Web Techniques, which appears after Advanced M Techniques. The lessons contained in this module are:

  • Parsing an HTML Web Page (UPDATED, formerly named Reading HTML Documents in the Web Based Data Sources module)
  • Parsing HTML as Text (NEW)
  • Basic Web Scraping (previously in Web Based Data Sources, content unchanged)
  • Advanced Web Scraping (previously in Web Based Data Sources, content unchanged)
  • Combining Multiple Web Pages (previously in Parameters and Custom Functions, content unchanged)
  • Pagination and DO/WHILE (previously in Advanced M Techniques, content unchanged)
  • NOTE: there is currently no quiz for this module

2023-03-31

Power Query Academy Updates:

  • Updated existing lesson:
    • Fuzzy Matching in Data Quality (in the Merging Data module)
  • Added two new lessons:
    • List of Values to Filter Columns (in the Advanced M Techniques module)
    • Convert Calendar to Fin Year (in the Date & Time Techniques module)

Power Query Recipes Updates:

  • Added two new cards:
    • Recipe 35.200 – Append all Tables in the Current Workbook
    • Recipe 60.950 – Convert a Julian Date to a Gregorian Date
  • Updated an existing card:
    • Recipe 60.530 (Fiscal Period Columns for 13 x 4-Week Calendars) – corrected the Fiscal Quarter formula

2023-06-29

Power Query Academy Updates:

  • Added four new lessons:
    • Convert UTC to Local DateTimes
    • Switching Time Zones
    • Convert UNIX Timestamp to Date
    • Convert Julian to Gregorian Dates

Power Query Recipes Updates:

  • Links to the new Power Query Academy lessons listed above have been added to the corresponding cards:
    • Recipe 60.900 – Convert a Unix Timestamp to a Valid Date
    • Recipe 60.910 – Convert UTC DateTime to Local DateTime
    • Recipe 60.920 – Switching DateTimes Zones
    • Recipe 60.950 – Convert a Julian Date to a Gregorian Date

2023-10-10

Power Query Academy Updates:

  • Revamping of Simple Transformation Techniques module, including:
    • Replacement of original Splitting Columns into Other Columns and Splitting Columns into Rows lesson with new Splitting Columns lesson that covers both transformations
    • Complete update of the Filtering Data lesson
  • Full updates to other existing lessons:
    • Direct Connectivity in Power BI Desktop (in the Data from Relative Sources module)
    • Power Query Values (in the M Deep Dive module)
  • Retired both the Basic and Advanced Web Scraping lessons (in the Advanced Data Modeling module), as the website linked to in the original example is no longer available.

Power Query Recipes Updates:

  • The caution note on Recipe 10.100 – Split Records into Columns has been adjusted.
  • The Power Query Academy lesson link on Recipe 10.105 – Split Records into Rows has been updated to go to the revised Splitting Columns lesson.
  • The steps in Recipe 20.150 – Remove Top x (Dynamic) Rows have been completely revised to reflect a better technique for this data transformation.

2023-12-22

Power Query Academy Update:
  • Added new lesson on how to Remove Top x (Dynamic) Rows from a data set (in the Advanced Data Reshaping module).

Power Query Recipes Update:

  • Complete rewrite of Recipe 20.150 – Remove Top x (Dynamic) Rows to make it easier to build and avoid pitfalls related to defining your ‘dynamic value’ correctly. (Links to the new lesson mentioned above are also provided on the recipe card and the Table of Contents.)

2023-03-27

Power Query Academy Updates:

  • Updated the Connecting to a PDF File lesson (in the Miscellaneous Data Sources module)
    • The new video shows the updated UI as when this lesson was originally recorded, the PDF connector was a Beta feature.
  • Updated the Basic Web Scraping lesson (in the Advanced Web Techniques module)
    • This lesson was previously removed as the website used in the demo was no longer available. We have revamped the demo to connect to a new website and we’ve added the lesson back into the course.

2024-06-28

Power Query Academy Updates:

  • New Semi Join lesson added to the Merging Data module

Power Query Recipes Updates:

  • New recipes added to Section 30 – Combining Data:
    • Left Semi Join – Recipe 30.117
    • Right Semi Join – Recipe 30.118
  • Recipe 30.100 updated to 9 Ways to Join Your Data

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 quizzes to validate your knowledge.

Course Content

Expand All
Module Content
0% Complete 0/1 Steps
Module Content
0% Complete 0/1 Steps

How does the subscription work?

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

Ken Puls

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.
Matt Allington

Matt Allington

Matt Allington is one of the founding partners of Skillwave.Training, and the founder of Excelerator BI in Australia. He is an expert in Power BI and DAX training, and has 30+ years of experience in using data to deliver business value. Matt is an author, blogger, and eight-time 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

Miguel Escobar is an Excel specialist turned Business Intelligence expert using the latest tools from Microsoft – Power BI and Power Query. He is the co-author of Master your Data with Excel and Power BI (formerly M is for (Data) Monkey), a blogger, and a Youtuber of powerful Excel tricks. Before joining the Power Query team at Microsoft, he helped found Skillwave.Training and was previously recognized as a Microsoft MVP and a Microsoft Certified Trainer (MCT).

FAQs

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?

If you subscription is set to auto-renew, our system will automatically renew your subscription for another year on your renewal date using the billing information on file. If you subscription is set to manual renewal, our system will generate a manual invoice and email it to you on the renewal date. About a month (4 weeks) before your next renewal 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 Billing page from your Account Dashboard and select the subscription to cancel from the My Subscriptions area. Once in the Subscription Details page, you can then choose to cancel your subscription from the Actions field. After your subscription expires, you will not longer have access to the course materials, including the downloadable 'Master Your Data' 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. Please note that because Skillwave is registered in Canada, we are required by Canada Revenue Agency to collect the 5% Goods & Services Tax (GST) on registrations made by Canadians.

What is your refund policy for this course?

We think you will love our training. However, we recognize that sometimes a course is not right for where you are in your learning journey. Provided that you have completed less than 10% of the course and it is within 14 days of the purchase date, we will un-enroll you from the course and either issue a credit note equal to your original purchase value which you can use to register in a more suitable course in the Skillwave course catalogue, OR defer your course enrollment data until you’ve had time to upskill to an appropriate level. Please see our full refund policy at https://skillwave.training/terms/

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.

Recommended Courses

Continue your learning journey with our other training offerings. We’ll help you take your skills to the next level.

  • Excel
  • Power BI
2h 30m
4 Modules
FREE
  • Power BI
  • DAX Studio
  • Power Pivot for Excel
10h 30m
5 Modules
$350