#3 Lesson | Learn to Code with Microsoft M-Language: Working with Primitive Values

6 min readMar 29, 2018

In the previous lesson, you wrote your first “Hello World!” program. You also learned about foundational concepts such as expressions, values, identifiers and variables. Make sure you memorize the terminology used to describe M-Language, as this will prevent you from misunderstandings when engaging with peers.

M-Langauge buckets values in two categories:

primitive values
structured values

Let’s look up the official definition of values:

“A single piece of data is called a value. Broadly speaking, there are two general categories of values: primitive values, which are atomic, and structured values, which are constructed out of primitive values and other structured values. For example, the values
1
true
3.14159
“abc”
are primitive in that they are not made up of other values. On the other hand, the values
{1, 2, 3}
[ A = {1}, B = {2}, C = {3} ]
are constructed using primitive values and, in the case of a record, other structured values.” Microsoft M-Language Specification

This concept is pretty straight-forward. However, M-Language comes with 15 different types of values.

It takes some time to literally memorize how each value is constructed before you can start using them properly. Unlike Excel formula language, M-Language does not come with IntelliSense (Microsoft’s intelligent code completion feature). I highly recommend you print out the entire Microsoft M-Language Specification and use it for as long as you haven’t familiarized yourself with all of different types of values (just as I did).

In this post, I will focus on few selected primitive values and how to work with them. You learned about two different types of values in the previous lesson. Text:

let
    Source = "Hello World!"
in
    Source

And numbers.

let
    x = 1,
    y = 2,
    z = x + y
in
    z

Let’s play a bit with our code before we move on to structured values. Type in the following code.

let
    Source = 1 + 1
in
    Source

Hit Done. The result is, as you might expect, the result of 1 + 1 = 2. Let’s tweak your code a bit.

let
    Source = "1 + 1"
in
    Source

What result do you get when you hit Done? This time, it’s 1 + 1 instead of 2. What happened? When using quotation marks, M-Language recognizes “1 + 1” as a text value, not as a number value. Let’s do another experiment.

let
    Source = "1"
in
    Source

Hit Done, and then go back to the Advanced Editor and tweak your code like this.

let
    Source = 1
in
    Source

Hit Done. Do you see any difference in the output? Nope. However, there is a fundamental, yet invisible distinction you need to be aware of. The former code sample returns “1” as a text value, while the latter gets you 1 as a number value. Why this matters? Imagine, you want to create customer identifiers that consist of the customer’s first and last name’s first letter and a unique number. Say, Paul Smith is PS1000. How would you do this? Check this out.

let
    FirstName = "P",
    LastName = "S",
    Customer = 1000,
    ID = FirstName & LastName & Customer
in
    ID

Hit Done and see what happens.

Oouch! We’re getting an error message here. “We cannot apply operator & to types Text and Number.” translates into a less crypitc language as follows: “You can’t mix and match two different types of values, in this case text and number.” How to fix this? There are two ways to do so.

let
    FirstName = "P",
    LastName = "S",
    Customer = "1000",
    ID = FirstName & LastName & Customer
in
    ID

That way, you say: “Hey, “1000” is a text value, not a number value.” You can only construct structured values of multple identical kinds of values. But what if you wanted to keep the third value 1000 a number. How would you construct the following, fourth structured value? Try this.

let
    FirstName = "P",
    LastName = "S",
    Customer = 1000,
    ID = FirstName & LastName & Text.From(Customer)
in
    ID

Hit Done and you’ll get the proper result here. What happened? We converted the number value 1000 into a text value using a function. We will discuss in more detail what functions are in upcoming lessions.

Let’s imagine for a minute, you were alone out there and you couldn’t figure out by yourself why you are getting an error message. What would you do? First, sign up at Microsoft TechNet. That’s exactly what I did when I first ran into problems with M-Language I couldn’t get solved by myself.

Here’s how I learned how to treat different types of values.

That’s the answer I received.

I did not instantly understand why I would need to rewrite my code like this, so I asked for a detailled explanation. That’s the answer I received.

You can read the entire thread here.

Lessons learned?

Don’t be shy. The sooner you start engaging with the Microsoft TechNet community, the better. Ask any question, no matter how stupid you think it is (I always ask tons of stupid questions).
If I can learn M-Language, so can you. I am just a regular guy who loves to learn and share his learnings along the way.
RTFM. Go to the Power Query M function reference webpage. It might read a bit cryptic in the beginning but the sooner you familiarize yourself with M-Language conventions, the better.

See you in the next lesson!

#3 Lesson | Learn to Code with Microsoft M-Language: Working with Primitive Values

This is my entire course

Written by SPRDsheet