What you'll learn

Directly write and edit Get & Transform and PowerQuery queries via "M".
The "M" language syntax and structure.
Encapsulate and re-use query logic to minimize duplication and improve query re-usability.
Build custom functions and solutions using "M".
Design and implement basic recursive algorithms via M.
Solve complex problems previously restricted to the VBA-realm with M.

Course content

4 sections • 29 lectures • 3h 53m total length

What Will You Do In This Course?2:37
Introduction1:02
Are You Ready For This?

Introducing the Advanced Editor6:30
Learn how to access the code and some of the first basics about the "M"ashup language.
Basic Syntax
Text Comparison Tools3:15
A brief video demonstrating a text comparison tool, which I use during this chapter to highlight differences between code sets.
M Language Intellitext4:34
Microsoft has added Intellitext support to the PowerQuery editor within Excel. Hooray!
Learning New Functions: The Easy Way9:18
Many users new to VBA learn commands via the macro recorder. The Get and Transform query editor offers a similar approach to learning functions by doing without diving deep into any documentation. This lesson looks at three different functions using a text comparer to see how the different usages generate different M Language commands.
Query Editor Concepts
Query Order of Operations8:10
This lesson dives into how each line within a query relates to each other line. In standard editor-only queries, each line is a one-use-only variable. When writing code directly, you can reuse and reorder how operations are performed. The attached resources include the starting and ending points from this video, plus the challenge file.
Challenge: Order of Operations16:36
This lecture consists of the recorded live-stream of the challenge solution walk-through plus the completed challenge file for reference.
Query Parameters10:07
Query parameters are parameter items built within the confines of the Get & Transform engine with specific syntax and uses. This feature has more value in Power BI than Excel; however, I use it on occasion in the following chapter generally for testing purposes. The Challenge file can be found in the resources, along with the starting and ending workbook from this video.
Challenge: Query Parameters14:37
A live-stream recording of the solution to the challenge with edits for time.
The M Language Basics Quiz

Introducing Functions11:46
Introduces the concept of re-usable query functions.
Challenge: Introducing Functions11:14
Solution to the challenge, recorded live-stream.
Optional Parameters: Extending Functions7:57
Use of optional parameters to extend and customize functions, with starting and ending workbook to follow lesson.
Challenge: Optional Parameters7:55
Recording of the live-stream solution.
Function Short-Hand3:45
Write shorter, more concise functions for simple commands.
Quiz: Writing Functions
Function in Function9:23
Write functions that return other functions.
Challenge: Function in Function6:43
A walk-through of the challenge with two separate solution files for comparison.
Records6:54
How to build records within M code. Used significantly in the recursion lessons.
The Underscore Parameter5:52
What the Each keyword means and how it pertains to functions for the following recursion lessons.
Quiz: Function Concepts and Supporting Details
Recursion: Part 1 - The Obvious Way8:52
How to write functions that call themselves, along with the general logic such functions should follow.
Recursion: Part 2 - The Proper Way12:47
How to use the List.Generate function and use it to perform recursive logic.
Functions: Brief Review6:46
Quick review of the concepts, how they fit together, and introduction to the Word Search exercise.
Quiz: Recursion and Overview
Word Search Exercise: Part 112:39
A walkthrough for the first half of the exercise solution.
Word Search Exercise: Part 25:34
The second-half walkthrough of the exercise solution

Lists, Tips and Tricks4:50
This lesson offers some tips and tricks for working with lists that can come in handy for different scenarios, including in the chapter exercise.
List.Accumulate9:34
A highly customizable function that can be used to create loops and much, much more.
Try and Otherwise: Catching Errors7:42
This lesson offers a brief overview of the try and otherwise keywords for catching and handling errors.
Try and Otherwise: Throwing Errors6:28
This lesson discusses how to throw customized errors for situations where a technical error doesn't occur but the results violate the intended rules of your function or query. The follow-up challenge is attached to this lesson as 4-2-challenge-v2.xlsx. [Updated: 5/9/2019]
Blackjack Challenge Solution9:37
This video will offer a walk-through of the creation for the solution to the Blackjack challenge posed for this chapter.

Requirements

Comfort with Excel's Get & Transform / PowerQuery tool or Power BI's PowerQuery tool.
Ready to learn to write some code and edit some formulas.
Willingness to expand horizons and think critically.

Description

Empower your ability to transform data..

The Query Editor that supports Get & Transform is powerful and allows even the newest Excel users to create powerful data manipulation queries with only their mouse and, occasionally, a few keyboard inputs. Behind each query is the powerful Mashup language, "M" for short. Picking up even a few tips and tricks with the language itself can allow you to create even more useful and powerful queries far faster than with strictly the editor alone.

In this course, you'll dive behind the Query Editor to learn about the syntax and structure of M Code so that you can build queries with the query editor and make manual adjustments to create results not achievable strictly through the graphical interface.

Save time with reusable query code..

Found yourself loving how quickly you can do so many things in the Query Editor but feel like you've been doing the same thing over and over? You probably have been and there's a solution for that!

With a few extra lines of code, you can turn many of your queries that you've duplicated and re-used over and over again into directly re-usable functions that will save you time, hassle, and dramatically improve your ability to maintain, update, and edit your queries.

In this course, you will learn about how to build reusable functions from existing queries or from scratch to give you the ability to build custom reusable user functions within your Query Editor.

Incorporate complex logic into your transformations..

Have you ever found yourself needing the results of an iterative calculation based upon a combination of fields or wanting to compute a compound calculation based upon other rows within the same data set?

These types of problems require complex logic that is generally the realm of true object oriented languages and other solutions rather than database computations like those generated from SQL.

The "M" language allows for constructing loops, writing recursive functions, and incorporating proper error trapping and management as part of your queries, allowing you to incorporate the detailed logic necessary for your solution.

In this course, you'll learn how to do each of these things within the M language, using the Query Editor to guide and test your code as you progress.

Apply the concepts together to solve problems..

The course has multiple challenges plus full-scale practice exercises to help you learn how to apply and combine the various components together. One large-scale exercise will have you applying everything you know about Get & Transform to build a series of queries and functions that can solve Word Search puzzles.

Each challenge and practice set has guided video explanations for how to arrive at the solution plus the solution files to peruse at your own pace.

Who this course is for:

Introductory Excel users who have completed an introductory level Get & Transform course.
Mid-level Excel users considering VBA as a next step.
Power Excel users with little-to-no experience in Get & Transform.
Analysts looking for newer and better ways to interact with data and apply complex transformations prior to Excel manipulation.

What you'll learn

Explore related topics

Course content

Introduction2 lectures • 4min

Basics of the M Language8 lectures • 1hr 13min

Functions14 lectures • 1hr 58min

Tips, Tricks, and Errors?5 lectures • 38min

Requirements

Description

Who this course is for: