Text Functions in Power Query M (150+ Examples)

Do you want to learn how to work with text data in Power Query? Power Query has a lot of different text functions that can help you manipulate text data. But it can be hard to know where to start.

In this article, I will show you all the text functions in Power Query and give you more than 150 examples to help you understand how to use them. You’ll learn how to change the case of text, split text, find and replace text, and much more.

It might seem a bit tricky at first, but by following the examples, you’ll be able to make your text data look just how you want it in no time! The aim of this post is not to describe all the intricacies, but to give you ready examples to start using text functions in Power Query. Let’s get started!

Table of contents

• Introduction
• Understanding Text Functions
  • Insert Text
  • Remove Text
  • Extract Text
  • Transform Text
  • Informational Functions
  • Encode to different Value
• Other Text Functions
• Conclusion

Introduction

Mastering text functions in Power Query is essential for anyone working with text values. It can make your data cleaning process easier and more efficient. With the right text functions, you can quickly and easily manipulate your text data into the right format.

Understanding Text Functions

Let’s dive deeper into how each function works. In the following chapters, we’ll cover everything from inserting text, removing text, extracting text, transforming text, and much more.

Insert Text

In Power Query you can insert text with different functions. The Text.Insert function allows you to add a text value into an existing text value at a specified position. This position starts at an index of 0.

= Text.Insert( "Fish", 0, "Big " )             // Returns "Big Fish"
= Text.Insert( "Arc", 3, "h" )                 // Returns "Arch"
= Text.Insert( "Ab", Text.Length( "Ab"), "c" ) // Combines functions, returns "Abc"

There are times where you want your text value to have a specific length. To address that, you can insert a value at the start or end of a string. The functions Text.Padstart and Text.PadEnd can perform that.

Both text functions have three arguments. First a text value, then the desired number of characters for the new string, and an optional character used for padding.

The default padding character is a space. It is used when the third argument is left empty. Yet you can provide the third argument with the padding character you desire. A typical use is to add leading zeros to a value.

= Text.PadStart( "Gorilla", 8   ) // Returns " Gorilla"
= Text.PadStart( "5440", 7, "0" ) // Returns "0005440"
= Text.PadStart( "544",  7, "0" ) // Returns "0000544"
= Text.PadStart( "54",   7, "0" ) // Returns "0000054" 

= Text.PadEnd( "Gorilla", 8   )  // Returns "Gorilla "
= Text.PadEnd( "5440", 7, "0" )  // Returns "5440000"
= Text.PadEnd( "544",  7, "0" )  // Returns "5440000"
= Text.PadEnd( "54",   7, "0" )  // Returns "5400000" 

Remove Text

The Text.Remove function allows you to remove all occurrences of a character. You can remove letters, numbers or any other character. The function has two arguments.

The first argument takes a text value, the second argument requires you to input one or more characters to remove input as single-character strings. You can enter this as a single text value or as a comma separated list with single-character strings.

// You can remove a single character or multiple.
= Text.Remove( "Rick de Groot", "o" )           // returns "Rick de grt"
= Text.Remove( "bigorilla", { "i", "e", "o" } ) // returns "bgrlla"
 
// The Text.Remove function is capital sensitive.
= Text.Remove( "Rick", "R" ) // Returns "ick"
= Text.Remove( "Rick", "r" ) // Returns "Rick"
 
// And accepts numbers, letters but also special characters.
= Text.Remove( "A/B_C", { "/", "_" } ) // Returns "ABC"
 
// You can also use a shortcut to generate the values to remove.
= Text.Remove( "power-BI99", { "A".."Z" } )           // "power-99"
= Text.Remove( "power-BI99", { "a".."z" } )           // "-BI99"
= Text.Remove( "power-BI99", { "a".."z", "A".."Z" } ) // "-99"
= Text.Remove( "power-BI99", { "0".."9" } )           // "power-BI"

The previous examples remove all provided characters from a string. There may be cases where you want to remove the provided characters at the start or end of a string. You can use Text.TrimStart and Text.TrimEnd for those cases.

= Text.TrimStart( "***gorilla***", "*" )      // Returns "gorilla***"
= Text.TrimStart( "* * gorilla", {" ", "*"} ) // Returns  "gorilla"
 
= Text.TrimEnd( "***gorilla***", "*" )        // Returns "***gorilla"
= Text.TrimEnd( "!845pqr", {"a".."z"} )       // Returns "!845"

Whereas the Text.RemoveRange function sounds very similar to Text.Remove, it does something completely different. The function allows you to provide an offset and a number of characters to remove. It then removes that number of characters starting from the offset position.

= Text.RemoveRange( "9-AR-C-85", 0, 5 ) // Returns "C-85"
= Text.RemoveRange( "9-AR-C-85", 3, 3 ) // Returns "9-A-85"

Extract Text

The next category of text functions focuses on extracting values from strings.

One of my favorite functions is the Text.Select function. You can instruct the function to keep all occurrences of one or more given characters. And with that it is the exact opposite of the Text.Remove function.

The Text.Select function has two arguments. First it needs a text value, and the second arguments contains the characters to keep.

= Text.Select( "jUxL2jE98Z", { "0" .. "9" } )    // Returns "298"
= Text.Select( "jUxL2jE98Z", { "a" .. "z" } )    // Returns "jxj"
= Text.Select( "jUxL2jE98Z", { "A" .. "Z" } )    // Returns "ULEZ"
= Text.Select( "jUxL2jE98Z", { "x", "E", "9" } ) // Returns "xE9"

The Text.Start function extracts the first characters of a string, whereas the Text.End function extracts the last characters of a text value. Both functions take a text value as first argument and require the number of characters to extract as second argument.

= Text.Start( "Life is good", 4 ) // Returns "Life"
= Text.Start( "Life is good", 7 ) // Returns "Life is"

= Text.End( "Life is good", 4 ) // Returns "good"
= Text.End( "Life is good", 7 ) // Returns "is good"

You may sometimes need to return values at a specific position in a string. You can return a single character using the Text.At function. It requires a text value as first argument and the offset position of your desired character as second argument.

To return multiple values you can use the Text.Range function in Power Query. It takes a text value as first argument and offset position as second. You can optionally provide the third argument to provide the number of characters to return. If this argument is left out, the function returns all characters starting from the offset position.

// Text.At always returns a single character
= Text.At( "Run@", 3 )     // Returns "@"
= Text.At( " - - - ", 1 ) // Returns "-"

// Text.Range can return a single character
= Text.Range( "Run@", 3 )    // Returns "@"
= Text.Range( "Run@", 3, 1 ) // Returns "@"
= Text.Range( "Run@", 5, 1 ) // Returns error: offset is out of range

// Or provides you with a range of values.
= Text.Range( " - - - ", 1 )    // Returns "- - - "
= Text.Range( " - - - ", 1, 3 ) // Returns "- -"

= Text.Range( "I am Groot", 2 )    // Returns "am Groot"
= Text.Range( "I am Groot", 2, 2 ) // Returns "am"

The Text.Middle function works identical to the Text.Range function. With one exception. When the Text.Middle function is out of range, it still returns a value. The Text.Range function however throws the error: “Expression.Error: The ‘count’ argument is out of range.“

= Text.Middle( "Hello World", 6, 10 ) // Returns World
= Text.Range( "Hello World", 6, 10 )  // Returns Error

The functions so far required a specific offset or number of characters to retrieve. Another set of functions extract values based on delimiters.

The function Text.AfterDelimiter extracts all text after your specified delimiter, whereas Text.BeforeDelimiter extracts everything before the delimiter. Both functions have 2 mandatory arguments and one optional argument.

The first argument requires a text value and the second argument takes the delimiter to find. By default both functions will look for the first delimiter they find.

If you don’t want to look for the first delimiter, you can use the third optional argument to indicate the number delimiters to skip before returning a value. You can even indicate from which side to skip your delimiters by using RelativePosition.FromStart and RelativePosition.FromEnd

= Text.BeforeDelimiter( "x-87-v", "-" )   // Returns "x"
= Text.BeforeDelimiter( "x-87-v", "-", 0 ) // Returns "x"
= Text.BeforeDelimiter( "x-87-v", "-", 1 ) // Returns "x-87"

// You can indicate from which side to search for the delimiter
// by using RelativePosition.FromStart and RelativePosition.FromEnd
// The functions by default use RelativePosition.FromStart

// Below formulas return: 1) "x", 2) "x", 3 ) "x-87", 4) "x"
1 = Text.BeforeDelimiter( "x-87-v", "-",  0 )
2 = Text.BeforeDelimiter( "x-87-v", "-", { 0, RelativePosition.FromStart } )
3 = Text.BeforeDelimiter( "x-87-v", "-", { 0, RelativePosition.FromEnd } )
4 = Text.BeforeDelimiter( "x-87-v", "-", { 1, RelativePosition.FromEnd } )

// Text.AfterDelimiter works with similar arguments.
= Text.AfterDelimiter( "x-87-v", "-" )    // Returns "87-v"
= Text.AfterDelimiter( "x-87-v", "-", 0 ) // Returns "87-v"
= Text.AfterDelimiter( "x-87-v", "-", 1 ) // Returns "v"
 
// Below formulas return: 5) "87-v", 6) "87-v", 7) "v", 8) "87-v"
5 = Text.AfterDelimiter( "x-87-v", "-",  0 )
6 = Text.AfterDelimiter( "x-87-v", "-", { 0, RelativePosition.FromStart } )
7 = Text.AfterDelimiter( "x-87-v", "-", { 0, RelativePosition.FromEnd } )
8 = Text.AfterDelimiter( "x-87-v", "-", { 1, RelativePosition.FromEnd } )

The shown examples look at text before or after a delimiter. Yet one text function is still missing. The Text.BetweenDelimiters function extracts text between a specified start and end delimiter.

Just like its two siblings this function accepts a start and end index to indicate the number of values to skip. And you can turn both into a list index to also indicate whether you should skip values at the end or start of the input.

= Text.BetweenDelimiters( "x-8-v-0", "-", "-" ) // Returns "8"
= Text.BetweenDelimiters( "x-8-v-0", "-", "v" ) // Returns "8-"
= Text.BetweenDelimiters( "x-8-v-0", "-", "0" ) // Returns "8-v-"

= Text.BetweenDelimiters( "x-8-v-0", "-", "-", 1 ) //Returns "v"
= Text.BetweenDelimiters( "x-8-v-0", "-", "-", 2 ) // Returns "0"

= Text.BetweenDelimiters( "x-8-v-0", "-" ,"-", 1, 0 ) // Returns "v"
= Text.BetweenDelimiters( "x-8-v-0", "-" ,"-", 1, 1 ) // Returns "v-0"
= Text.BetweenDelimiters( "x-8-v-0", "-" ,"-", 0, 1 ) // Returns "8-v"
= Text.BetweenDelimiters( "x-8-v-0", "-" ,"-", 0, 2 ) // Returns "8-v-0"

// Below three expression are identical. The function uses 
// RelativePosition.FromStart and an index of 0 by default.
= Text.BetweenDelimiters( "x-8-v-0", "-", "-" )       // Returns "8"
= Text.BetweenDelimiters( "x-8-v-0", "-", "-", 0, 0 ) // Returns "8"
= Text.BetweenDelimiters( "x-8-v-0", "-", "-", 
                     { 0, RelativePosition.FromStart }, 
                     { 0, RelativePosition.FromStart } ) // Returns "8"

= Text.BetweenDelimiters( "x-8-v-0", "-" ,"-", 
                     { 1, RelativePosition.FromStart }, 
                     { 0, RelativePosition.FromStart } ) // Returns "v"
= Text.BetweenDelimiters( "x-8-v-0", "-" ,"-", 
                     { 2, RelativePosition.FromStart }, 
                     { 0, RelativePosition.FromStart } ) // Returns "0"
// Once you reach the end of the input increasing the index has no impact
= Text.BetweenDelimiters( "x-8-v-0", "-" ,"-", 
                     { 2, RelativePosition.FromStart }, 
                     { 1, RelativePosition.FromStart }) // Returns "0"

// The function looks for the second "-", and then from there looks back 
// to the start of the input value
= Text.BetweenDelimiters( "x-8-v-0", "-" ,"-", 
                      { 1, RelativePosition.FromStart }, 
                      { 2, RelativePosition.FromEnd } ) // Returns "x-8"

= Text.BetweenDelimiters( "x-8-v-0", "-" ,"-", 
                      { 2, RelativePosition.FromStart }, 
                      { 2, RelativePosition.FromEnd } ) // Returns "x-8-v"

= Text.BetweenDelimiters( "x-8-v-0", "-" ,"-", 
                      { 2, RelativePosition.FromEnd }, 
                      { 2, RelativePosition.FromEnd } ) // Returns "x"

Transform Text

This chapter focuses on text functions that help in transforming and cleaning text values. Some are relatively easy with a one or two arguments. Let’s have a look at how you can use Text.Lower, Text.Upper, Text.Proper, Text.Trim, Text.Clean, Text.Reverse, Text.Repeat and Text.Combine.

= Text.Lower( "abcDEF" ) // Returns "abcdef"
= Text.Upper( "abcDEF" ) // Returns "ABCDEF"

// Capitalizes the first letter of each word
= Text.Proper( "abcDEF" )               // Returns "Abcdef"
= Text.Proper( "i like cats and dogs" ) // Returns "I Like Cats And Dogs"

// Removes control characters from a string
= Text.Clean( "R#(lf)i#(cr)ck" ) // Returns "Rick"

You can also focus on removing spaces, reversing text or combining them.

// Removes leading & trailing characters, white space by default
= Text.Trim( "    gorilla    " )              // Returns "gorilla"
= Text.Trim( "    gorilla    ", " " )         // Returns "gorilla"
= Text.Trim( "- -gorilla- -", "-" )           // Returns " -gorilla- "
= Text.Trim( "- -gorilla- -", "-" )           // Returns " -gorilla- "
= Text.Trim( "- -gorilla- - ", { "-", " " } ) // Returns "gorilla"
 
= Text.Reverse( "gorilla" )  // Returns "allirog"
= Text.Repeat( "abc", 3 )    // Returns "abcabcabc"
 
// The second argument allows you to input an optional separator
= Text.Combine( { "abc", "123" } )            // Returns "abc123"
= Text.Combine( { "abc", "123" }, "-" )       // Returns "abc-123"
= Text.Combine( { "ab", "12", "." }, "-" )    // Returns "ab-12-."

A unique text function to combine and format your texts is Text.Format. As arguments the function first takes a format string, then an argument from a list or record and optionally a culture code. You can achieve similar results with the Text.Combine function. Yet a benefit of this function is that you can provide a culture code.

= Text.Format( "The #[Subject] wants a #[Food].",
                [ Subject = "Gorilla", Food = "Banana" ]  )
// Returns values from a record: "The Gorilla wants a Banana."
 
= Text.Format( "#{0}, #{1} and #{2} are friends.",
               { "Rick", "Ben", "Mila" } )
// Returns values from a list: "Rick, Ben and Mila are friends."
 
// Adding a culture code allows you to force local formatting
 
= Text.Format( "Please pay us €#{0} before #{1}.",
               { 49.99, #date(2022, 5, 1 ) }, "EN-us" )
// Returns "Please pay us €49.99 before 5/1/2022."
 
= Text.Format( "Please pay us €#{0} before #{1}.",
               { 49.99, #date(2022, 5, 1 ) }, "NL-nl" )
// Returns "Please pay us €49,99 before 1-5-2022."

The opposite of combining values is splitting them. Power Query has the text functions Text.Split and Text.SplitAny to split values. As arguments the functions both take a text value and then one or more separators as second argument. After execution the function returns a list.

= Text.Split( "06-18-18-22", "1" )    // Returns { "06-", "8-", "8-22" }
= Text.Split( "06-18-18-22", "-" )    // Returns { "06", "18", "18", "22" }
= Text.SplitAny( "06-18-18-22", "-" ) // Returns { "06", "18", "18", "22" }

// For multiple separators, Text.SplitAny does not require a list.
= Text.SplitAny( "6|8|8-2", "-|" )     // Returns { "6", "8", "8", "2" }
= Text.SplitAny( "RickDeGroot", "er" ) // Returns { "RickD", "G", "oot" }

Very similar is the Text.ToList function. The Text.ToList function takes a string and returns a list containing all single-character text values of this string.

=Text.ToList( "Rick" ) // Returns { "R", "i", "c", "k" }

Another category of functions focusses on replacing values. Power Query offers the functions Text.Replace and Text.ReplaceRange that both have their own approach to this. Text.Replace replaces all occurrences of a specified text value and replaces these with a new text value. The replacement is case senstive.

= Text.Replace( "I enjoy sunny days", "enjoy", "love" ) // Returns "I love sunny days"
= Text.Replace( "my friend sees my show", "my", "his" ) // "his friend sees his show"

The Text.ReplaceRange removes a given amount of characters and replaces these with the provided text value. It works very similar to the Text.RemoveRange function, with the difference that it allows you to replace the removed range with a provided value.

As arguments it takes a text value, an offset to start the replacement, the number of values to replace and lastly ends with the new text value.

= Text.ReplaceRange( "RickVanGroot", 4, 3, "De" ) // Returns "RickDeGroot"
= Text.ReplaceRange( "RickVanGroot", 4, 3, "" )   // Returns "RickGroot"

Informational Functions

Informational functions are a category of text functions that analyze text values for certain criteria. An important function is Text.Contains. The Text.Contains function checks whether a text value contains a string. By default it does this case sensitive. However, you can provide the function with a comparer to alter the behavior and make it case-insensitive comparison.

= Text.Contains( "Red roses", "Red" )                             // Returns true
= Text.Contains( "Red roses", "red" )                             // Returns false
= Text.Contains( "Red roses", "red", Comparer.Ordinal )           // Returns false
= Text.Contains( "Red roses", "red", Comparer.OrdinalIgnoreCase ) // Returns true

Then there are functions that check whether a value starts or ends with a given string. Power Query uses Text.StartWith and Text.EndsWith for that.

= Text.StartsWith( "I got the Power", "I got" )                             // true
= Text.StartsWith( "I got the Power", "i got" )                             // false
= Text.StartsWith( "I got the Power", "i got", Comparer.Ordinal )           // false
= Text.StartsWith( "I got the Power", "i got", Comparer.OrdinalIgnoreCase ) // true
 
= Text.EndsWith( "Biking to home", "home" )                             // true
= Text.EndsWith( "Biking to home", "HOME" )                             // false
= Text.EndsWith( "Biking to home", "HOME", Comparer.Ordinal)            // false
= Text.EndsWith( "Biking to home", "HOME", Comparer.OrdinalIgnoreCase ) // true

Other functions can return valuable information about a value. The Text.Length returns the length of a text value. You can use this information again in other functions to for example make sure each character has the same length.

= Text.Length( "Rick" )      // Returns 4
= Text.Length( "BIGorilla" ) // Returns 9

In its most basic form, the Text.PositionOf function returns the first position of a given substring in a text value. The first argument takes the text value and the second requires the substring. When a substring can’t be found, the function returns -1.

By default the function returns the position of the first occurrence of the substring. However, you can use the occurrence parameter in the optional third argument to change this behavior. You can use:

Occurrence.First (0) – returns the position of first occurrence of the searched value
Occurrence.Last (1) – returns the position of last occurrence of the searched value
Occurrence.All (2) – returns a list of positions of all occurrences of the searched value

= Text.PositionOf( "We all want pasta", "a" ) // Returns 3

// An index number determines which position of the value to return

// The first occurrence of a value is returned by default, returns 3
= Text.PositionOf( "We all want pasta", "a", Occurrence.First ) 
= Text.PositionOf( "We all want pasta", "a", 0 ) 

// Occurrence.Last returns the last occurrence, returns 16
= Text.PositionOf( "We all want pasta", "a", Occurrence.Last ) 
= Text.PositionOf( "We all want pasta", "a", 1 ) 

//  Occurrence.All returns all occurrences, returns { 3, 8, 13, 16 }
= Text.PositionOf( "We all want pasta", "a", Occurrence.All ) 
= Text.PositionOf( "We all want pasta", "a", 2) 

// You can also return the 2nd position from the start of the string, returns 8
 Text.PositionOf( "We all want pasta", "a", Occurrence.All ) {1}

// By default the function searches capital sensitive. // Returns -1
= Text.PositionOf( "We all Want pasta", "w", 1 ) 

//  Add an optional comparer to control the comparison. Returns 7
= Text.PositionOf( "We all Want pasta", "w", 1, Comparer.OrdinalIgnoreCase ) 

Then there is the Text.PositionOfAny function that returns the position of the first occurrence of the characters provided in a list. The function only takes a list of single-character strings. Optionally you can provide an occurrence parameter to indicate which occurrence position to return.

= Text.PositionOfAny( "You like guacemole", { "e" } )      // Returns 7
= Text.PositionOfAny( "You like guacemole", { "e", "u" } ) // Returns 2

// Occurrence parameters determine the position(s) to return
= Text.PositionOfAny( "You like guacemole", { "e", "u" }, Occurrence.First ) // 2
= Text.PositionOfAny( "You like guacemole", { "e", "u" }, Occurrence.Last )  // 17
= Text.PositionOfAny( "You like guacemole", { "e", "u" }, Occurrence.All )   
// Returns { 2, 7, 10, 13, 17 }

Encode to different Value

You can use different text functions to transform values from one data type to another.

= Text.From ( 123 ) // Returns "123"

// Tests if Power Query recognizes the string as a number
= Text.InferNumberType( "€11.11" ) // Returns number

// Returns DataFormat.Error: We couldn't match 'ABC' with any known number pattern.
= Text.InferNumberType( "ABC" ) 

In M different functions use Unicode character values. To find out which character represent these you can use the functions Character.FromNumber and Character.ToNumber.

= Character.ToNumber( "!" )  // Returns 33
= Character.FromNumber( 33 ) // Returns "!"
= Character.FromNumber( 35 ) // Returns "#"

// You can use these characters to return a list of values. 
// You can return the unicode characters representing 33-35:
= { "!" .. "#" } // Returns = { "!", """", "#" }

Other Text Functions

Lastly, there are some function that may be useful, yet I have not worked with them. This article won’t go into more detail. This is regarding:

– Text.NewGuid
– JSON.FromValue
– Guid.From
– Text.FromBinary
– Text.ToBinary

Conclusion

That’s it! Now you know how to use all the text functions in Power Query. With more than 150 examples and explanations, you have a great understanding of how to manipulate text data. You’ve learned how to change the case of text, split text, find and replace text, and much more.

Remember, practice makes perfect. The more you use these functions, the more comfortable you’ll become with them. If you have any questions or if you have any other methods that you use, feel free to share them in the comments below. I hope this article was helpful and you’re now more comfortable working with text data in Power Query. Are there text functions you want to see more content on? Let me know in the comments.

If you like learning from examples, also make sure to check out the posts:

• Lists in Power Query M / List Functions (200+ Examples)
• Replace Values in Power Query M (Ultimate Guide)

Enjoy Power Query!