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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
***** Related Links *****
Best Practices For Power BI Report Development
Best Practices For Transforming Data In The Query Editor
Data Loading And Transformation Best Practices
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
