This tutorial will help you understand more about the M code found inside the Advanced Editor.
Some people prefer to call it “M formula language” or “M function.” You can use it to filter and combine your data from multiple data sources.
How To Access Power BI’s M Language
To access the M code, select a query. For this example, we’ll click the Sales query.
After that, click Advanced Editor to open the editing window.
In the Advanced Editor window, you can view or modify the entire code that appears for the selected query.
The code looks complicated, but you don’t need to learn how to write the said codes. All that matters is your understanding of how the code works and what it does.
In a nutshell, these codes are records of the transformations or actions that were done for the selected query.
Understanding M Code Inside The Advanced Editor
The first thing you can see inside the M code is the source. In this part, you can see the location where the system has retrieved the data.
As you can also see, the system automatically identifies the data type for the Order Date, Ship Date, etc.
Next, you can see the records of the transformations that were done to the data such as Rename Column, Removed Column, Duplicated Column, and many more.
You can see a lot of codes here because of the different data transformations. However, you don’t really need to write these codes because you can make it work just by clicking or selecting something from the ribbon.
Let’s say you want to change something. You want to rename Order Date to Ordering Date. You can do it inside the Advanced Editor.
If you change the column name to Ordering Date, you can see the particular step inserted in all the prior steps of the code. Once you click Done, the corresponding column in the query is also updated.
Now that you have an initial idea of how things work in the M code, I’ll show you an example where you can get an error.
Let’s say you want to move the Warehouse column next to the Warehouse Code column. In the Applied Steps section, you can see the corresponding record for Reordered Columns.
Now, this is where you have to be very careful. If you go back to the Advanced Editor and change the Order Date column to Ordering Date again, you’ll get an error.
Because the columns have been rearranged, the Reordered Column part in the code records every single column that was affected. Thus, if you want to make a change in the affected columns, you need to change it under the Reordered Column part.
On top of that, you also need to make the same changes to the other record of Order Date at the beginning of the code.
After you click Done, you can fix the error and see the changes applied to the Order Date column.
***** Related Links *****
Iterating Functions In DAX Language – A Detailed Example
Formatting DAX Code In Power BI
How To Use Natural Language Query (Q&A) In Power BI
Conclusion
I hope you learned the basics of M code and Advanced Editor through this tutorial.
Only about 1% of Power BI users actually use the M language on this platform. Nevertheless, it’s a good and worthy feature to learn and experiment with as much as you can.
Since you can only use the M language or M code in querying or cleaning your data, you need to be more concerned about writing high-quality DAX because that’s where you can harness the analytical power of Power BI.
Hopefully, this gave you a better idea about the Advanced Editor and what is happening behind the scenes.
Thanks!
Sam
