Power Query M Language Tutorial And Mastery

Power Query M Language Tutorial And Mastery

3 comments

This tutorial will discuss about how to successfully implement a piece of M code and solve common errors inside the Power Query Editor. You’ll also learn how to effectively practice and master the M language in Power Query.

Power Query M Language Implementation

The success of implementing a piece of M code depends on how well you can resolve any issue you encounter. In this tutorial, you’ll learn about the most common errors when trying to transfer M code from another PBIX file or when trying to implement a piece of M code you found on the forum.

But first, if you have a Power Query question, it’s important to supply the data along with your PBIX file because resolving an issue may require a change in the data setup. Failure to supply the data will lead to a data source error on the part of whoever is trying to provide you support because they don’t have access to the source.

There is a way around providing a separate file, but it’s only applicable to samples with 3000 values or less across both columns and rows. So if your table doesn’t exceed the 3000 cell limit, you can copy it in its entirety and paste it in the Create Table window by using the Enter Data option. The data will be transferred to binary and will be embedded in the file.

power query M language

File Location Parameter In Power Query M Language

Another way to help the person providing you support is by incorporating a file location parameter. So that when they update the file, all queries in it will be restored. To demonstrate that, follow these four steps.

First, go to your Windows Explorer and copy the file path.

Second, go back to the Power Query Editor and select Manage Parameter. Next, select New Parameter and give the parameter a name.

Third, use names without spaces or special characters to avoid using the quoted notation when you refer to your parameters. Set the Type to Text and the Suggested Values to List Of Values. When you pass the file through the forum, you can select your location from the file list.

Finally, open the Advanced Editor window and replace the hard-coded file path with your parameter name.

power query M language

Common Errors In Power Query M Language

1. Cyclic Reference

The error expression below occurs when the output of a selected query is passed to a function argument of a step within the query. In this example, you can see that the query name is passed to the Table.AddColumn function in one of its steps.

power query M language

The first argument requires a table type. In most cases, you will find that it references the previous step in your code. The previous step can be found in the Applied Steps pane which is located on the right side of the preview pane.

It’s not required to reference the previous step; you can just pass another table reference to that function. So in the source file, look up the step name in the Applied Steps pane to see the shape of the table that was passed to the function. That can help you translate the step to your own file.

2. Unrecognized Name

This expression error occurs when the name of a table argument that passed to a function doesn’t exist or was misspelled.

power query M language

Keep in mind that Power Query is case-sensitive. In most cases, it will reference the previous step which you can look up in the Applied Steps pane. But then again, it’s not required.

When troubleshooting this type of error, look at the table shape passed to the function in the source file and compare that to your own file. Also, make sure steps haven’t been renamed.

3. Text String

This expression error is an example where a table argument that was passed to a function was actually passed as a text string.

power query M language

Step names that include spaces or special characters need to have the quoted notation hash or pound sign in front. The names should be enclosed in a set of double quotes. When copying and pasting step names, always make sure to select the full name.

4. Type Mismatch Error

Another common expression error is the type mismatch error.

power query M language

These types of errors tend to pop when you start making modifications to the M code. In the error message above, a datetime value was passed to a function that required a date data type.

The solution to this error is to convert the data type of the value before passing it as an argument to another function. In this case, you can use the Date.From function to convert the datetime value into a date value.

There are many types of mismatch errors. Whatever the case, the system will always state the required data type.

5. Name Doesn’t Exist

Another common expression error is when the name of a column reference, passed as an argument to a function, doesn’t exist or is misspelled.

power query M language

Make sure that the column names match. You can also try changing the column reference. If the column names match and you still get the error, see if there are spaces or special characters in the name.

6. Token Comma

This is an expression syntax error about Token Comma.

power query M language

Just like how each function takes a comma-separated list of function arguments, the let expression takes a comma-separated list of the variable names and values. To resolve this type of error, make sure that each step, except for the final step, has a comma at the end. This error can also be generated by missing closing parentheses or brackets at the end of a function.

7. Token Identifier

The Token Identifier Expected error usually happens when M code is copied because the quote signs go haywire.

power query M language

To fix the error, you have to find and replace all quote signs. You can also use an application called Visual Studio Code which you can download for free at code.visualstudio.com.

Other Errors In Power Query M Language

Here are other errors that you are likely to encounter when implementing M code from the forum.

  • Token Literal Expected – this means that the next thing in the formula is expected to be a value, column, or function.
  • Token RightParen Expected – this means that a closing bracket or parenthesis is expected to close the formula
  • Comma Cannot Precede A RightParen – this means that a comma cannot be directly in front of a closing parenthesis or bracket.
  • Invalid Literal – often occurs when a text string hasn’t been closed with a double quote sign.
  • Token EoF Expected – occurs when an invalid keyword or function name is used.

Power Query M Language Mastery

Mastery requires long-term commitment and investment. It’s all about doing, getting inspired, practice, and repeat. But even without mastery, you can achieve amazing improvements in a relatively short period of time.

If you understand and are able to bring the concepts covered in these posts into practice, you’ll know how to address errors when they occur. You’ll also be able to play with function parameters like replacing hard-coded values with dynamic values or writing custom M functions.

All you need to do is start exploring on your own. You’ll learn the most by allowing yourself to make mistakes and then find multiple ways to resolve them. This can be time-consuming, but it’s also very rewarding. Once you’re able to do that, you can hone your skill.

Power Query M Language Best Practices

1. Formula Bar

One of the general best practices on how you can expand your knowledge on Power Query is to make sure to have the formula bar visible at all times. You can explore the formula language specification that describes the structure of the language and revisit it after you’ve practiced for some time.

You can go on a scavenger hunt through the online formula reference, which lists all of the functions and their specifications. You can read them, copy the examples into the Advanced Editor window, and study the results.

2. Standard Library

Another best practice is to explore the standard library using the #shared keyword because there are differences compared to the online formula reference. You can also do a web search for other content like practical examples or problems that others have encountered.

That is often referred to as Learn by Googling. You can look for a function name or a type of operation or transformation, but the results will depend heavily on how well you’ve defined the search criteria and how well others have described their case.

3. Practice And Review

The most important thing is to practice and deliberately spend more time inside the Query Editor. Also, find a better balance between the amount of time you spend learning DAX, data visualizations, and M code.

You can also review your past work and incorporate these best practices. Participate in activities like Enterprise DNA’s Accelerator Program and Power BI Challenge. Build on that by answering questions on the forum to deepen your understanding and solidify your knowledge.

There will always be more to learn and discover. So set a goal that you can achieve like dedicating 30 minutes a day to your own study.

Conclusion

Implementing M in your code requires practice and mastery. Errors may occur if a variable, function, or any other entity is used incorrectly. Once M is used correctly, your data report will provide valuable and insightful results.

Melissa

***** Related Links *****
Best Practices For Power BI Report Development

Best Practices For Transforming Data In The Query Editor
Data Loading And Transformation Best Practices

***** Related Course Modules *****
Power Query M Masterclass Part One

Best Practices & Feature Reviews
Data Transformations & Modeling

***** Related Support Forum Posts *****
Best Way To Optimize Data Model With Intermediate Tables

Best Practice To Get Data From REST API That Requires Paging
Looking For A Best Practice That’s Speedy
For more best practices to review see here….

3 comments on “Power Query M Language Tutorial And Mastery”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.