Get a month of TabletWise Pro for free! Click here to redeem 
TabletWise.com
 

Advanced Get and Transform/PowerQuery: The Mashup Language

Learn the intricacies of writing "M"ashup for the PowerQuery engine within Microsoft Excel and Power BI to supercharge your data acquisition and analysis.

Advanced Get and Transform/PowerQuery: The Mashup Language

Learn the intricacies of writing "M"ashup for the PowerQuery engine within Microsoft Excel and Power BI to supercharge your data acquisition and analysis.
3
Views
3:56:37
Share the link to this class
Copied
A brief overview of the course, what you will do, and what you should know before you begin.
Introduce yourself to the query editor, some of the features at play, and the syntax of the Mashup language.
Learn how to access the code behind the query along with the first basics concerning the "M"ashup language.
Briefly learn about how to use text comparison tools that can be useful in learning the language with the help of the wizard interface.
Recent updates to Excel and Power BI have added intellitext to the editors. This video will briefly cover how those can help you with your queries.
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.
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.
This lecture consists of the recorded live-stream of the challenge solution walk-through plus the completed challenge file for reference.
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.
A live-stream recording of the solution to the challenge with edits for time.
Starting point for the Order of Operations video
A completed version from the video for reference.
The starting point for the Order of Operations challenge.
A completed version of the challenge workbook for reference.
The starting point for the Query Parameters video to follow along with.
The completed version of the workbook from the video for reference.
The starting point for the Query Parameters challenge.
The solution to the Query Parameters challenge for reference.
Write, design, and re-use functions following these lessons on syntax, recursion, function parameters, and more.
The starting point for the introducing functions challenge
The solution file for the introducing functions challenge.
A recorded live-stream for the solution to the challenge.
Use of optional parameters to extend and customize functions, with starting and ending workbook to follow lesson.
Write shorter, more concise functions for simple commands.
Functions can return other functions.
Starting point for the Function-in-Function challenge. Has two solution versions.
One possible solution to the challenge.
An alternative solution to the challenge.
A guided tour to creating one of the solutions.
How to build records within M code. Used significantly in the recursion lessons.
What the Each keyword means and how it pertains to functions for the following recursion lessons.
How to write functions that call themselves, along with the general logic such functions should follow.
How to use the List.Generate function and use it to perform recursive logic.
Quick review of the concepts, how they fit together, and introduction to the Word Search exercise.
The starting point for the word search exercise. Good luck!
The first half of a guided tour to solving the Word Search problem.
The second half of a guided tour to solving the Word Search problem.
The completed exercise file for reference.
A few parting lessons covering some special tips and tricks for working with lists and how to use error trapping within the M language.
This lesson offers some tips and tricks for working with lists that can come in handy for different scenarios, including in the chapter exercise.
A highly customizable function that can be used to create loops and much, much more. Pairs well with List.Generate.
This lesson offers a brief overview of the try and otherwise keywords for catching and handling errors.
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.
The starting point for the challenge presented in the Try and Otherwise: Throwing Errors video.
A guided solution to the blackjack problem posed in the challenge.

Empower your ability to transform data

The Query Editor that supports Get and 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 any 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 and 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.

Requirements

You should be familiar with PowerQuery in Excel or Power BI.

A brief overview of the course, what you will do, and what you should know before you begin.
Introduce yourself to the query editor, some of the features at play, and the syntax of the Mashup language.
Learn how to access the code behind the query along with the first basics concerning the "M"ashup language.
Briefly learn about how to use text comparison tools that can be useful in learning the language with the help of the wizard interface.
Recent updates to Excel and Power BI have added intellitext to the editors. This video will briefly cover how those can help you with your queries.
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.
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.
This lecture consists of the recorded live-stream of the challenge solution walk-through plus the completed challenge file for reference.
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.
A live-stream recording of the solution to the challenge with edits for time.
Starting point for the Order of Operations video
A completed version from the video for reference.
The starting point for the Order of Operations challenge.
A completed version of the challenge workbook for reference.
The starting point for the Query Parameters video to follow along with.
The completed version of the workbook from the video for reference.
The starting point for the Query Parameters challenge.
The solution to the Query Parameters challenge for reference.
Write, design, and re-use functions following these lessons on syntax, recursion, function parameters, and more.
The starting point for the introducing functions challenge
The solution file for the introducing functions challenge.
A recorded live-stream for the solution to the challenge.
Use of optional parameters to extend and customize functions, with starting and ending workbook to follow lesson.
Write shorter, more concise functions for simple commands.
Functions can return other functions.
Starting point for the Function-in-Function challenge. Has two solution versions.
One possible solution to the challenge.
An alternative solution to the challenge.
A guided tour to creating one of the solutions.
How to build records within M code. Used significantly in the recursion lessons.
What the Each keyword means and how it pertains to functions for the following recursion lessons.
How to write functions that call themselves, along with the general logic such functions should follow.
How to use the List.Generate function and use it to perform recursive logic.
Quick review of the concepts, how they fit together, and introduction to the Word Search exercise.
The starting point for the word search exercise. Good luck!
The first half of a guided tour to solving the Word Search problem.
The second half of a guided tour to solving the Word Search problem.
The completed exercise file for reference.
A few parting lessons covering some special tips and tricks for working with lists and how to use error trapping within the M language.
This lesson offers some tips and tricks for working with lists that can come in handy for different scenarios, including in the chapter exercise.
A highly customizable function that can be used to create loops and much, much more. Pairs well with List.Generate.
This lesson offers a brief overview of the try and otherwise keywords for catching and handling errors.
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.
The starting point for the challenge presented in the Try and Otherwise: Throwing Errors video.
A guided solution to the blackjack problem posed in the challenge.

About the instructors

Explainly

Expert authors. Advanced topics. Clear explanations.
Share the instructor profile
Copied

Explainly offers online courses on powerful topics centered around the premise of instruction followed up with implementation through practical use.

Our founders, Philip Trick and Ahad Amdani, are experts in their respective fields and offer courses designed around their expertise with a history of success building online courses for other providers, including many popular Udemy publishers.

Top Software Classes

New Software Classes

All Classes
Free for 30 Days
   The video is currently being processed.
   An error occurred while uploading the video. Please upload another video.
   Please upload the required file.
Quiz: #TITLE#
Questions: #QUESTIONS_COUNT#
Quiz: #TITLE#
Question /#QUESTIONS_COUNT#
Quiz: #TITLE#
Result: You correctly answered out of questions. Result: You correctly answered out of question. Result: You correctly answered out of questions attempted. Result: You correctly answered out of question attempted. Result: You did not attempt any question.
0
Saves
3
Views
This class has not been saved

Sign Up