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.


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

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


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.


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


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 number next.

Until then, happy coding!

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

      1. Ben Rogers

        Another solution (depending on what you are trying to do) is using the Text.Combine() function, since that ignores nulls by default.

  2. PJW

    Hi Ben,

    I love your blog. I’m trying to use Power Query to Transform a spreadsheet and combine data from multiple columns into a single cell with a carriage return between each of the column inputs. Here is an example of code:

    = Table.AddColumn(#"Renamed Columns", "Merged", each Text.Combine({"ServerDate: ", [ServerDate],"#(cr)#(lf)","CaptureDate: ", [Priority], "/7/2", [UserScore], "19 2:52:45 PM"}), type text)

    The Power Query out looks like:

    ServerDate: 3/7/2019 4:52:52 PM
    CaptureDate: 3/7/2019 2:52:45 PM

    The output is good when do the transformation in Power Query, but when I Close & Load to convert to an Excel Spreadsheet, the carriage return disappears.

    The Excel Output looks like:

    ServerDate: 3/7/2019 4:52:52 PMCaptureDate: 3/7/2019 2:52:45 PM

    How can I retain the carriage return when converting to Excel?

    1. Ben Gribaudo Post author

      Thank you for your kind words. On the line breaks, I am not sure. 🙂 If I output text with a line break in it, I see the same “all on one line” effect you see. However, if I click on the cell for editing, the line breaks are visible in the edit box located near the top of the screen. If I then click okay to complete editing, the line breaks are reflected in the cell. So, the line breaks are being passed from Power Query to Excel but for some reason aren’t displayed in the cell out of the box. Interesting!

      1. Ben Rogers

        When using #(lf) and loading to Excel, you have to set the cells in Excel to Wrap Text for the line breaks to display.

  3. Chris Springer

    Hello Ben,
    I just found your website.

    I have a frustrating problem with power query. I am trying to import a table containing “part numbers”. Some of the part numbers are alpha numeric & some are a text decimal number like “67111.2”. When I the originating data table has these as a text field. When power query imports this field, the “number items “ get extra decimals. 67111.2 turns into 67111.9999997.

    Obviously I cannot change the Company’s part numbers.

    Can you help??

    1. Ben Gribaudo Post author

      In your Power Query, is there an auto-created “Change Types” step? What happens if you click on this step then change the appropriate column’s type to text?


Leave a Reply

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