1. Introduction to Advanced Editor
Now that we have a good understanding of some of the more advanced features in Power Query, let’s dig deeper into the backbone of Power Query: the Advanced Editor. We will also learn more about M Code, the language of Power Query.
2. What is the advanced editor?
The Advanced Editor is a tool in Power Query which allows you to see the “source code” of your query and change it.
Power Query works by translating each transformation step to a line of M code, the language of Power Query. This is like when you record macros in Excel and it gets translated to Visual Basic for applications.
DAX and M Language are both parts of Power BI but they have some important differences. We use DAX to create measures and analyze data, while M language is used to extract, transform, and load data.
3. The difference between M code and DAX
DAX, which stands for Data Analysis eXpressions is used to create metrics such as sums, averages, and so on.
These metrics do not actually change our data, they simply use data to calculate new values.
So when you filter a column with a DAX measure, the underlying data remains in it's original state.
Just like Excel, DAX functions are not case-sensitive.
If you want to load, edit, or even create data to analyze, you will need to use Power Query or M Language which comes from data Mashup. There are many transformations you can apply to your data once it is loaded. You have already learned how to apply many of these transformations such as removing columns or renaming them, pivoting tables, and merging queries. One more difference between DAX and M is that M code is case-sensitive.
4. M Language and the applied steps
Although this may seem complicated at first, M code is really just a translation of the steps you have been applying so far in this course into a sequential programming language.
Any step you apply to the data is translated to a corresponding line of M code, while any line of M code we write gets translated to an applied step. So it is a 1:1 relationship.
5. Writing M language
Similar to other programming languages, you can store variables as well as define functions to be used in the context of your query. We will learn how to write our own custom functions in this lesson which is a very useful feature for applying some more advanced transformations to your data as well as for writing more succinct code.
Finally, you may also add comments to your M code by using a double forward slash prefix. This can help your code be more readable for others.
6. Intro to M language
The M Language defines several value types for use in data transformations. Many of these are analogous to the data types we have already seen in Power Query such as Number, Text, Logical, and Date. More abstract value types such as lists and tables also exist, and are used by the Power Query engine as a way to group data together.
Once you learn some applications of M Language, you will understand how lists can be used to store important values for functions.
Tables are the most common data structure in M Language.
Tables consist of column names and lists of values that are organized into those columns. Here we can see the output of our example code produces a table with two columns: Index and Value consisting of the 3 rows of data we passed as lists to our table constructor function.
7. Let's practice!
Let's try this out ourselves and see what can be achieved with the Advanced Editor and some knowledge of M code.