#5 Lesson — Part 2 | Learn to Code with Microsoft M-Language: Real World Project — Mashing up Data

7 min readMar 29, 2018

We did quite a lot in the Part 1 of this lesson.

First, we converted a list and a record into a table, and then we merged those two newly created tables with another table using a unique index. If you went through each single lesson, you might have made an interesting observation in Lesson 5: We did quite complex stuff (compared to writing “Hello World!” in Lesson 1) without writing a single a single line of code.

You can use M-Langauge without actually writing code

So, why bother learning to code at all? Seriously, that’s a good question. My favourite book on M-Language “M is for (Data) Monkey” by Ken Plus and Miguel Escobar does exactly that: It teaches you to perform pretty complex queries with M-Language without writing too much code (maybe a line here and there).

Like I said in the introduction to my course, M-Language has one tremendously great advantage over other programming languages: You can write code, you can click through the UI … or both. That way, it’s impossible to fail.

Having a fundamental understanding of M-Language is not a prerequisite to actually use it in practice. However, I firmly believe it’s good to be able to look under the hood, read and understand M-Language code. Especially, if you want to get started with programming and later move from a a query language like M-Language to a object oriented programming language such as Python. You might not be able to instantly understand every single line of M-Language code, but mastery will come with practice.

First learn to read, then to write

One thing I learned along the way, when learning to code with M-Language, is that there is a major similarity with aquiring a natural language. Reading comes before writing. Once you master the very basics (as we did in the previous lessions), you will be able to read most of the M-Language code. The more you read, the more comfortable you are going to be at actually writing code — not only in M-Language, but further down the road also in other languages.

Let’s do exactly that.

Let’s read the code that was autogenerated for us.

Open up the SupportData Excel Sheet. Go to the Data Ribbon and select Queries & Connections.

Right click CountryList, select Edit and hit the Advanced Editor on the Home Ribbon. Your code should look like this.

If you need a refresher on the very basics of M-Language, please go back to the previous lessons. Now, let’s take a closer look at the code above.

The first five lines constitute a Let Expression. “An expression is a formula used to construct values.” as you can read in the Microsoft M-Language Specification. Now, let’s go through the code, line by line:

We took a list, which is nothing but a structured value … consisting of primitive text values
We then converted the list into a table, we decided not to use any delimiters and to show extra columns as errors
We then renamed the Table Column name from Column1 to Countries
Finally, we added an Index Column which strarted with 0

This result of this computation was then presented as an output underneath in. Let’s look briefly into the UI which we used to autogenerate the code.

This is our list, which we converted to a table.

We selected the default settings as far as delimiters and error handling goes.

Next, under Query Settings > Properties > Name we changed the default name to a more meaningful one.

Last but not least, we added an Index Column.

We used three different table functions to get the job done.

Table.FromList
Table.RenameColumns
Table.AddIndexColumn

Eeach of them might look a bit odd to you in the beginning. Let’s take a coser look at the third function.

#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1)

First, the identifier left to the equal sign might look a bit off to you. Remember you first “Hello World!” line of code? It looked very much like this.

let
    Source = "Hello World!"
in
    Source

Alternatively, your identifier could look like this.

let
    #"This is my first line of code" = "Hello World!"
in
    #"This is my first line of code"

In case your identifier consists of more than one word, you need to use the pound sign and put your identifier into parentheses. That’s exactly the case with our Table.AddIndexColumn function.

Now let’s take a look what’s happening right to the equal sign. One great source is the Power Query M function reference. On this page, go to the Table Functions Section.

Scroll down the Table functions list.

Click on Table.AddColumn. You will find an explanation how to work with this function, what the components are of which it consists … and an example.

Look at the code. Notice one thing?

It’s the same code twice! Why so? Because we’re all humans, and humans make errors. Here’s just another example of this universal truth :-) Let’s grab the highlighted code above. In your open Excel Workbook, launch a Blank Query and go to the Advanced Editor, just as you did in previous lessons.

Next, remove both quotation marks right to the Source identifier in the Let expression and dump the code here. Just dump it.

Hit done and see what happens.

Boom! Your Table.AddColumn function added a column which gets you the Total Price of Price + Shipping for each record. Let’s open the Advanced Editor for a minute again. You see the original records.

Next, the Table.AddColumn function looks at each records and calculates the Total Price.

You might have noticed that I am not a big theory guy. In my course, I provide theory in a homeopathic dosage, as I prefer to learn by playing … just grab code, dump it into your Advanced Editor, see what it does (or what it doesn’t) , manipulate it, see what happens … and then go back to the theory if needed.

On a side note: This is not the best example in the world, as adding calculated columns & writing measures is something you would do with DAX, Microsoft’s Data Analysis Expressions language (I touched upon it briefly in the foreword to this course). M-Language is great for mashing up data from different sources, or if you want to clean up data, which we are going to discuss in the next lesson. We already mashed up data in the previous lesson.

Also, one important thing to know: M-Language has no knowledge whatsoever of indentations. You could, if you wanted, write the entire Let expression in one, ridiculously long line of code. For now, let’s put a space at the end of our Let expression.

Likewise, you can place indentations whenever you think they make sense in order to make your code more readable. I invite you to play around with your code, before we move on to the next lesson.

Have fun!

#5 Lesson — Part 2 | Learn to Code with Microsoft M-Language: Real World Project — Mashing up Data

You can use M-Langauge without actually writing code

First learn to read, then to write

Let’s read the code that was autogenerated for us.

This is my entire course

Written by SPRDsheet