1. M Language and Advanced Editor demo
The Advanced Editor allows us to take a peek at the “source code” of our query and see the block of M code that resulted from these automated translations.
Let’s create a new “Blank Query”, and take a look at the Advanced Editor code:
Obviously, there isn’t much going on here, so the code looks quite uninteresting. All we can really see is the Source step, which has a blank value assigned to it. In addition, we see the “let” and “in” statements, which tell Power Query where our query expression begins and ends.
Let’s look at the Advanced Editor for a different query with some data loaded in it.
The “Source” and “Navigation” steps are usually automatically added by Power Query when you first load the data into Power BI by choosing a data source. These lines of M code collectively refer to the path where your data is located and how Power BI is connecting to it. In this case, we are using the “OData.Feed” M Function to connect to an OData data source located at this URL. Then we are instructing power query to select the table called “Orders” and load the data inside it.
Now close the Advanced editor and add a step to this table; let’s remove the “EmployeeID” Column.
Now if we were to click on the Removed Column Step in the Power Query applied steps pane, we can see a preview of the M Code that was just created by adding this step, here in the formula bar:
Let’s also open the advanced editor and confirm that the code was added there as well:
Yes, we can now see the last step here is “Removed Columns”, where the column that we removed is passed to the function in a list (which is represented by the curly brackets) and fed to the Table.RemoveColumns() function. Here, we can change the column we wish to remove just by editing the “EmployeeID” in the function parameter. Let’s do that and see what happens:
As expected, the EmployeeID column has returned in our data and now the CustomerID column has been removed. If we wish, we could also remove a second column by adding another column name to the list, separated by a comma.
In the top-right of the Advanced Editor window, we can see a dropdown menu called “Display Options”, this menu has a set of modifications that make it easier to interface with the Advanced editor. You should experiment with these options to find a configuration that is comfortable for you.
It’s important to note each step of the query will always reference the name of a previous step in the code. This represents the sequential transformations that take place on your data and ensures that the steps are executed in the correct order. We can see this here.
Always make sure when you change or add any steps in the advanced editor that you keep this referential structure intact.
Great, now let’s solve some exercises to develop our knowledge and practical skills with Power Query and the Advanced Editor!
2. Let's practice!