1. Discovering M Language
We will now focus on M Language. This Power BI coding language is mainly used during our data import process. In order to see this in action, let’s open Power Query Editor.
Here we are able to make different transformations and manipulations of our data - and this is done using M Language. It is also used for creating the connections we have.
Let’s examine this in action, first with the CSV data source. We select the table created from this data source in the Queries panel. On the right, we can see any changes that have already been applied to this table. In the home menu bar, we have a section called Query. Let’s click on Advanced Editor.
The window that opens displays an area where M Language syntax is contained. We won’t go into too much detail on the actual syntax, but it is important we identify areas in this feature that can assist us.
Let's examine some parts of the syntax here. If we move the window so that we can see the Applied Steps for this data model in the Power Query Editor, we can actually see that some of those titles are also visible in the Advanced Editor window. We have Source, Promoted Headers, and Changed Type in this Let section.
After each word, we have an equal sign and then the M code that is defining what is being applied to the table in the data model. We will focus on the Source part. The M code in this section indicates that it is a CSV document that is being used to bring in data.
We can also clearly see the folder that the file is located in, and which is used to connect to Power BI. After this, we see several details related to the CSV file itself, such as the number of columns. Let’s see what happens if we change something in the location section.
We get an error message in Power Query Editor, which states that Power BI could not identify the path to bring in the data from the CSV. Let’s change it back again from the Advanced Editor window. Perfect, it all works again.
In fact, when in the Power Query Editor we can also see the M code used in the formula bar that we see above the table information. If we select the Source label from the applied steps panel and click on the down arrow at the end of the formula bar, we can see all the information related to the Source M code that we saw in the Advanced Editor. We can also make changes here too.
Now let’s look at the ODBC connection we have created in Advanced Editor.
We can actually see the data source name that we have connected to, as well as the SQL code that was used in the Advanced options box to filter out information during the data connection process. Let's change the SQL code that was used from here. We will delete the last part and click Done.
No errors are shown by Power BI, so so far so good. Let’s click on Close&Apply.
The value in our card visual has changed from 639 thousand to 20 million! So we have been able to change the data connection using Power Query Editor and M Language. Great, now it’s your turn.
2. Let's practice!