Power Query M Primer (Part 6): Types—Intro & Text (Strings)

, , , ,

Examine an executing Power Query mashup under a microscope and what to you see? Data pulsing down pathways between expressions. Increase your microscope’s magnifying power. As you zoom in on one of the flows, what you’re viewing transforms from a blurred stream of data into the individual data items that make up that flow.

As you study what you see, you notice that the data items flowing by fit into groupings based on the kind of value they contain: some hold text, others are made up of a date, a time or a datetime; yet others are true/false values, then there are numbers…and it looks like there are even more categories beyond these!

In this post, we’ll begin exploring these categories—we’ll begin exploring the kinds of values supported by the M language. In programming parlance, these kinds of values” are called types.

Types can have special behaviors associated with them. For example, date and time have a special rule around addition: add a date and a time together and you get back a datetime! We’ll investigate these special rules.

Also, we’ll discuss the literal syntax associated with each type. In this context, ”literal” implies that you literally want to write out a value in code. You might, for example, literally want to set variable rate to the hand-selected value of 100. The syntax you use to code up the expression that produces the literal value you want is called literal syntax. Different types have different literal syntax rules. We’ll talk about these rules, as well.

There’s a lot to cover. In this post, we’ll explore the specifics of text (strings). If we explored all of the types in this post, it would get mighty long, so we’ll save the others for later.

Series Index

Text (Strings)

Text Literals

In M code, to type up text, you have one choice. Unlike some programming languages, M’s literal strings come in only one flavor. No single-quoted vs. double quoted. No heredocs. No interpolated strings. Plain and simple—just double-quoted Unicode strings.

"Hello, World!"

If the string itself needs to contain the double-quote character, the simplest way to accommodate that need is to escape each double quote by doubling it:

"He said ""run"" and so I did." // evaluates to the string: He said "run" and so I did.

Strings can also span multiple lines.

"Hello
World"

However, at times (perhaps often), you may find it more convenient to code multi-line strings using a single line of source code. To do this, use an escape sequence. These sequences start with “#(“, contain an escape code (or several codes, as we’ll see in a moment) and end with “)”.

Power Query supports three special control character escape codes:

Control Character Escape Codes
cr Carriage Return
lf Line Feed
tab Tab

Below, a line feed character is encoded into a string using an escape sequence.

"Hello#(lf)World"

Multiple escape codes can be combined inside an escape sequence by separating them with commas. However, no extra whitespace is allowed (interesting—this is one of the few places in Power Query where whitespace between language elements is relevant).

"Hello#(cr,lf)World"
"Hello#(cr)#(lf)World" // evaluates to the same string as the preceding

Short and long hexadecimal Unicode values can also be used. This ability comes in handy when you need a string to contain a character that’s not easy to type on your keyboard—or when you want to show a simple example, like the below which demonstrates using both short and long hexadecimal syntax to insert a total of two spaces between the two words.

"Hello#(0020,00000020)World" // evaluates to: Hello  World

The escape sequence’s syntax raises a question: What happens if you want #( to literally be in a string? You’re not trying to use it as an escape sequence. Instead, you want those characters to literally be included in the string.

The answer? You escape it by…drumroll please…using an escape sequence! Below, the “#” symbol inside the escape sequence is a special escape code called an escape escape.

"Part Code #(#)(2501)" // becomes string: Part Code #(2150)

Operators

Once you have a value of type text—whether you hand-coded it using a string literal or received it as the output value from another expression, you can work with it using several operators.

Comparison

Text support standard comparison operators (=, <>, >=, >, <=, <). Using them is straightforward.

"a" = "a" // evaluates to true
"a" > "b" // evaluates to false

Comparison are case-sensitive. To compare in a case-insensitive way, you’ll need to fall back to a library function. For example:

Comparer.Equals(Comparer.OrdinalIgnoreCase, "A", "a") // evaluates to true

Concatenation

Text values can be combined (concatenated) using the combination operator (&):

"Good" & " " & "Morning!" // evaluates to: Good Morning!

However, Power Query does not implicitly convert non-text values to text when they are concatenated with text. The below isn’t allowed. Text and numbers can’t be directly concatenated together.

"You have " & 5 & " left."

Don’t worry, though! It’s easy to adapt the above so it works. Simply use a library function to convert the number to text.

"You have " & Text.From(0.5) & " left." // evaluates to: You have 0.5 left.
"You have " & Number.ToText(0.5, "P") & " left." // evaluates to: You have 50.00% left.

Combining text with null produces null.

"Something Profound" & null // evaluates to null
null & "Something Profound" // evaluates to null

Hopefully, you already know this, but Power Query’s library includes a number of functions related to type text. Unfortunately, at least as of the present, regular expression support isn’t a part of that list of functions.

Next Time

There are many more types to cover. Maybe we should look at type numeric next.

Until then, happy coding!

3 thoughts on “Power Query M Primer (Part 6): Types—Intro & Text (Strings)

  1. Konrad

    Interesting post, thanks. How would you replace the null efficiently with an empty string, i.e. convert it to a “Text-like/friendly” null, so that you don’t null the entire string.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *