Power Query: Presenting Text

Welcome to our Power Query blog.  This week, I look at how text output can be presented.

Back in Power Query: Let it Be, I created the simplest piece of M code:

The output is recognised by Power Query as text, and text tools are available.

If I want to manipulate the presentation of my text string, there are some other simple methods I can use.

If I type “Hello” and “World” in separate lines in the Advanced Editor, the text is split over two lines. If I look at the step for this line, the code looks a little different:

The M code is actually stored as

The text has been manipulated with an escape sequence. There are a number of definitions of escape sequences; this is the Microsoft version:

Escape sequences are typically used to specify actions such as carriage returns and tab movements on terminals and printers. They are also used to provide literal representations of nonprinting characters and characters that usually have special meanings, such as the double quotation mark (").

In this case, the escape sequence #(lf) is telling power query to insert a linefeed. This is one of three control escape codes supported by Power Query, the others are:

• #(cr)          carriage return
• #(tab)        tab.

In the next screen, I use a tab instead of the linefeed:

I can also combine my escape codes into a sequence.

This applies a linefeed and then a tab. 

I can also write this by grouping the escape sequences within the brackets (). 

For those of us familiar with them, short and long hexadecimal Unicode values can also be used.  One definition of a hexadecimal in a computing context is: 

Hexadecimal is a convenient way to express binary numbers in modern computers in which a byte is almost always defined as containing eight binary digits. 

Hexadecimals can represent a variety of characters.  A simple example is the hexadecimal for a space, which in its long form is 00000020, and in in its short form is 0020.

In the previous screen, the escape sequences only appear in the Advanced Editor, though I typed them into the step! 

There are times when I may want the actual characters to appear, rather than be translated – this can apply to double quotes “, or the beginning of an escape sequence #(.  In each case the answer is add more!

In order to output “Hello World” complete with the speech marks, in the Advanced Editor I need to type “””Hello World”””.

For the escape code #(lf) to appear, I need to add the escape code (#) between the # and the (lf).  Whilst I am unlikely to want to output escape sequences, users do have a habit of using ‘special’ character in reference numbers!

Come back next time for more ways to use Power Query!