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

• Introduction, Simple Expressions & let (part 1)
• Functions: Defining (part 2)
• Functions: Function Values, Passing, Returning, Defining Inline, Recursion (part 3)
• Variables & Identifiers (part 4)
• Paradigm (part 5)
• Types—Intro & Text (Strings) (part 6) [this post]
• Types—Numbers (part 7)
• Types—The Temporal Family (part 8)
• Types—Logical, Null, Binary (part 9)
• Types—List, Record (part 10)
• Tables—Syntax (part 11)
• Tables—Table Think I (part 12)
• Tables—Table Think II (part 13)
• Control Structure (part 14)
• Error Handling (part 15)
• Type System I – Basics (part 16)
• Type System II – Facets (part 17)
• Type System III – Custom Types (part 18)
• Type System IV – Ascription, Conformance and Equality’s “Strange” Behaviors (part 19)
• Metadata (part 20)
• Identifier Scope & Sections (part 21)
• Identifier Scope II – Controlling the Global Environment, Closures (part 22)
• Query Folding I (part 23)
• Query Folding II (part 24)
• Extending the Global Environment (part 25)
• More to come!

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.

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:

Strings can also span multiple lines.

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.

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).

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.

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.

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.

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

Concatenation

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

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.

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.

Combining text with null produces 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 number next.

Until then, happy coding!