Power Query MasterClass (EOMC3)

Power Query MasterClass (EOMC3)

Power Query MasterClass is the most in-depth course on data modeling in Excel. This is the most advanced Power Query course, meaning it’s assumed you already know the basics of Power Query in Excel. If you’re looking for basics, go for Power Query Course (EOXLG). In that Power Query course, we mostly use commands that are available in a graphical interface. In this masterclass, however, we’ll dive into M code, the language of Power query. Writing our own code allows us to use techniques and methods that Power Query doesn’t even have. Besides looking at how code works, we’ll look into Query Folding, which regulates how efficient (or fast) your query is.

MasterClass comes in two flavors, a 15-hour course or an in-depth 25-hour course.

The lecturer is Gašper Kamenšek, the only Slovenian Excel MVP (Most Valuable Professional), a title awarded by Microsoft for excellence in the respective field. Gašper is an Excel lecturer of more than 15 years. He shares his knowledge through various helpful videos on Excel Olympics YouTube YouTube channel. You can even find a dedicated Power Query playlist here: Power Query Playlist. Another way you can learn from Gašper is by visiting his Excel Unplugged blog, one of the most-read Excel blogs in the world.

Who is Power Query MasterClass (EOMC3) for?

The masterClass is appropriate for everyone who is already familiar with Power Query features but has never written any M code. We’ll look further than the built-in features and actually learn to use code to unleash Power Query’s full potential. Before attending this course, we recommend attending Power Query Course (EOXLG). Ideally, you can attend Power Pivot MasterClass (EOMC2), which nicely complements this course.

What will I learn?

After attending this course, you’ll use all data transformations with ease and learn to love Excel and Power Query even more.

Chapters and topics

Upgrading Excel tools
  • Flash Fill with formulas – Column From Examples
  • “Vlookup” for several parameters – Outer Joins
  • “Vlookup” omitting all non found – Inner Joins
  • “Vlookup” that returns only missing values – Anti Joins
  • Advanced text formatting
Import data from other files
  • Txt
  • CSV
  • Excel (the same workbook)
  • Excel (second workbook)
  • Access
  • Import from the entire folder
Import from the web
  • Importing exchange rates
  • Xml import
  • Dynamic import
Import from the SQL database
  • Import tables from the Azure SQL database
  • Import views from the Azure SQL database
  • Own T-SQL connection for data acquisition
Transforming data
  • Pivot Command
  • Unpivot command
  • Working with dates
  • Work with text
  • Working with lists
Error mamagement
  • Try … Otherwise
  • Removes errors
Import from other sources
  • Import from the SharePoint Online list
  • Import from the Exchange mailbox
Create custom import functions
  • Create custom import functions
Manage Power Query import via VBA in Excel
  • Manage Power Query Import via VBA in Excel
CUSTOM COURSE

Create your custom course

With our course configurator, you can create education to your liking.
Choose only the topic that interests you, you need it or you want to learn it.

Create course