Text functions – Excel & Power Query comparision

For many Excel users, the transition to Power Query can be difficult. Having spent years working with Excel’s functions, moving to Power Query seems like an alien environment.

The good news is that many of Excel’s text functions have equivalent functions in Power Query.

The purpose of this post is to provide a reference to find and apply the equivalent Power Query text functions. It is not an exhaustive list but includes the functions a user will most likely use.

Table of Contents

Download the comparison table: Click the button below to join the Insiders program and gain access to the Quick Reference comparison table created for this post.

File name: 0183 Excel v Power Query Function Comparision.pdf

Using Power Query text functions

Power Query text functions can be used in various places; so, let’s start our exploration here.

User interface

Many text functions, but not all, are available as individual transformations within the user interface. We can find them in the Add Column > From Text and Transform > Text Columns sections of the ribbon.

Add Column > From Text

Add Column - Text Functions

Transform > Text Columns

Transform - Text Functions

The options in the user interface are individual transformations.

For more advanced transformations that combine functions, we can use a custom column, the formula bar, or the advanced editor.

Custom column

A custom column allows us to add a calculation for every row of the Table.

From the ribbon, click Add Column > Custom Column to display the Custom Column dialog box.

Custom Column Dialog Box

The example above demonstrates the Text.Start function (equivalent to Excel’s LEFT function) to extract the first characters from a text value.

After clicking OK, this creates a value for every row of the Table.

Custom Column

Formula bar

The formula bar displays the M code for each transformation step. For applying more advanced skills, we use the formula bar to either write formulas from scratch or edit existing formulas.

Formula Bar

The example above shows the formula generated for the custom column created above.

Advanced editor

The advanced editor displays the M code for an entire query; this includes every formula for every transformation.

To open the advanced editor, click View > Advanced Editor.

Advanced Editor

The screenshot above displays the code for the entire query. The highlighted code is the Text.Start function used in the custom column section.

Column by examples

The last place we may see Power Query’s text functions is using Add Column > Column From Examples.

While we do not write the formulas ourselves, Power Query identifies patterns and provides a formula that achieves the required result.

Column From Examples

As shown in the screenshot, Power Query determined Text.Start is the formula to achieve the result shown in the First Characters column.

With this method, we may not write the formula ourselves, but understanding the structure helps to ensure that Power Query’s suggestion is correct.

Functions

Now, it’s time to look at the functions. They are presented below in alphabetical order by Excel function name.

NOTE

While many Excel and Power Query functions are equivalent, one may have additional optional arguments, which provide more flexibility. These are not covered in full in this post.


CHAR / UNICHAR

ExcelPower Query
Name:CHAR / UNICHARCharacter.FromNumber
Description:Returns the character specified by the code numberConverts a number to a text character.
Syntax:CHAR(number)
UNICHAR(text)
Character.FromNumber(number)
Example:=CHAR(65)=Character.FromNumber(65)
Result:“A”“A”
Notes:
  • CHAR includes character numbers from 1 to 255; any character codes greater than 255 require the UNICHAR function.

CLEAN

ExcelPower Query
Name:CLEANText.Clean
Description:Returns a text value with all control characters removed.Removes all nonprintable characters from text.
Syntax:CLEAN(text)Text.Clean(text)
Example:=CLEAN(“Example
text”)
=Text.Clean(“Example
text”)
Result:“Exampletext”“Exampletext”

CODE / UNICODE

ExcelPower Query
Name:CODE / UNICODECharacter.ToNumber
Description:Returns a numeric code for the first character in a text string.Converts a character to a number value.
Syntax:CODE(text)
UNICODE(text)
Character.ToNumber(text)
Example:=CODE(“A”)=Character.ToNumber(“A”)
Result:6565
Notes:
  • CODE includes the standard ASCII characters from numbers 1 to 255, any character codes greater than 255 require the UNICODE function.

CONCAT / CONCATENATE

ExcelPower Query
Name:CONCATText.Combine
Description:Combines text from multiple ranges and/or strings.Returns a text value that is the result of joining all text values.
Syntax:CONCAT(text1, [text2],…)
CONCATENATE(text1, [text2],…)
Text.Combine(texts, [separator])
Example:=CONCAT(“Example”, ” “, “text”)=Text.Combine({“Example”, ” “, “text”})
Result:“Example text”“Example text”
Notes:
  • If a separator is required between each word, look at the TEXTJOIN function.
  • The & character is an alternative to using a function. & combines text strings in both Excel and Power Query.
  • CONCAT (introduced in Excel 2019) is a more flexible replacement for the CONCATENATE function. CONCATENATE still exists for compatibility with previous versions.

FIND

ExcelPower Query
Name:FINDText.PositionOf
Description:Finds the start position of a text value within another (case-sensitive)Returns the occurrence of substring in a string and returns its position.
Syntax:FIND(find_text, within_text, [start_num])Text.PositionOf(text, substring, [occurrence], [comparer])
Example:=FIND(“mp”, “Example
text”)
=Text.PositionOf(“Example text”, “mp”)
Result:43
Notes:
  • FIND is case insensitive. For a case-insensitive version, check out the SEARCH function.
  • Power Query returns the zero-based position; therefore, the results between Excel and Power Query appear different.
  • Power Query’s [occurrence] argument is zero-based. So, to find the second instance, the [occurrence] will be 1.
  • Power Query has an additional function, Text.PositionOfAny, which allows a list of characters to be used as the basis of the search.

LEFT

ExcelPower Query
Name:LEFTText.Start
Description:Returns the leftmost characters from a text value.Returns the count of characters from the start of a text value.
Syntax:LEFT(text, [num_chars])Text.Start(text, count)
Example:=LEFT(“Example text”, 7)=Text.Start(“Example text”, 7)
Result:“Example”“Example”

Notes:

  • The [num_chars] argument is optional. If excluded, only 1 character is returned.
  • Power Query also has the Text.StartsWith function, which returns True/False when the text starts with a specified string.
    Syntax: Text.StartsWith(text,substring,[comparer])
    Example: Text.StartsWith(“Example Text”,”Example”)
    Result: True

LEN

ExcelPower Query
Name:LENText.Length
Description:Returns the number of characters in a text string.Returns the number of characters in a text value.
Syntax:LEN(text)Text.Length(text)
Example:=LEN(“Example text”)=Text.Length(“Example text”)
Result:1212

LOWER

ExcelPower Query
Name:LOWERText.Lower
Description:Converts text to lowercase.Returns the lowercase of a text value.
Syntax:LOWER(text)Text.Lower(text, [culture])
Example:=LOWER(“Example text”)=Text.Lower(“Example text”)
Result:“example text”“example text”

Notes:

  • The optional [culture] argument within Text.Lower is not covered in this post.

MID

ExcelPower Query
Name:MIDText.Middle
Description:Returns a specific number of characters from a text string starting at a specified position.Returns a substring up to a specific length.
Syntax:MID(text, start_num, num_chars)Text.Middle(text, start, count)
Example:=MID(“Example text”, 4, 2)=Text.Middle(“Example text”, 3, 2)
Result:“mp”“mp”

Note:

  • The start argument for Text.Middle is zero-based. Therefore, 0 for the start argument in Text.Middle is equivalent to 1 for start_num argument in MID.
  • In Power Query, Text.Range is an alternative to Text.Middle. The difference is that Text.Range returns an error when there are insufficient characters to return a value.
  • If we only require a single character, a Power Query alternative is Text.At:
    Syntax: Text.At(text,index)

PROPER

ExcelPower Query
Name:PROPERText.Proper
Description:Capitalizes the first letter in each word of a text valueCapitalizes the first letter in each word of a text value.
Syntax:PROPER(text)Text.Proper(text, [culture])
Example:=PROPER(“Example text”)=Text.Proper(“Example text”)
Result:“Example Text”“Example Text”

Notes:

  • The optional [culture] argument within Text.Proper is not covered in this post.

REPLACE

ExcelPower Query
Name:REPLACEText.Replace
Description:Replaces characters within text.Replaces length characters in a text value starting at a zero-based offset with the new text value.
Syntax:REPLACE(old_text, start_num, num_chars, new_text)Text.ReplaceRange(text, offset, count, newText)
Example:=REPLACE(“Example text”, 5, 3, “ination”)=Text.ReplaceRange(“Example text”, 4, 3, “ination”)
Result:“Examination text”“Examination text”

Notes:

  • The offset argument in Power Query’s Text.Replace is zero-based.
  • To remove text, rather than replace it, we can also use Text.RemoveRange in Power Query.
    Syntax: Text.RemoveRange(text, offset, count)

REPT

ExcelPower Query
Name:REPTText.Repeat
Description:Repeats text a given number of times.Returns a text value composed of the input text value repeated a number of times.
Syntax:REPT(text, number_times)Text.Repeat(text, count)
Example:=REPT(“Example”, 3)=Text.Repeat(“Example”, 3)
Result:“ExampleExampleExample”“ExampleExampleExample”

ExcelPower Query
Name:RIGHTText.End
Description:Returns the rightmost characters from a text value.Returns the count of characters from the end of a text value.
Syntax:RIGHT(text, [num_chars])Text.End(text, count)
Example:=RIGHT(“Example text”, 4)=Text.End(“Example text”, 4)
Result:“text”“text”

Notes:

  • The [num_chars] argument is optional. If excluded, only 1 character is returned.
  • Power Query also has the Text.EndsWith function, which returns True/False when the text ends with a specified string.
    Syntax: Text.EndsWith(text,substring,[comparer])
    Example: Text.EndsWith(“Example Text”,”Text”)
    Result: True

ExcelPower Query
Name:SEARCHText.PositionOf
Description:Finds the start position of a text value within another (case-insensitive)Returns the occurrence of substring in a string and returns its position.
Syntax:SEARCH(find_text, within_text, [start_num])Text.PositionOf(text, substring, [occurrence], [comparer])
Example:=SEARCH(“mp”, “Example
Text”)
=Text.PositionOf(“Example Text”, “mp”, 0, Comparer.OrdinalIgnoreCase)
Result:43
Notes:
  • SEARCH is case insensitive. For a case sensitive version, check out the FIND function.
  • Look at the FIND function for additional usage notes.

SUBSTITUTE

ExcelPower Query
Name:SUBSTITUTEText.Replace
Description:Substitutes new text for old text in a text string.Replaces all occurrences of a substring with a new text value.
Syntax:SUBSTITUTE(text, old_text, new_text, [instance_num])Text.Replace(text, old, new)
Example:=SUBSTITUTE(“Example text”, “Example”, “New”)=Text.Replace(“Example text”, “Example”, “New”)
Result:“New text”“New text”

Notes:

  • SUBSTITUTE has an optional [instance_num] argument which is not available in Power Query.

TEXT

ExcelPower Query
Name:TEXT
Description:Formats a number and converts it to text
Syntax:=TEXT(value, format_text)

Example:=TEXT(50, “0000”)
Result:0050

Notes:

  • There is no direct equivalent to the TEXT function in Power Query. However, combining many text functions together, can create a similar effect. Look at the following functions for options:
    • Text.From – Returns the text representation of a number, date, time, datetime, datetimezone, logical, duration or binary value.
    • Text.Format – Returns formatted text that is created by applying arguments from a list or record to a format string.
    • Text.PadStart – Returns a text value padded at the beginning to make it at least a specified length of characters.
    • Text.PadEnd – Returns a text value padded at the end to make it at least a specified length of characters.

TEXTAFTER

ExcelPower Query
Name:TEXTAFTERText.AfterDelimiter
Description:Returns text that occurs after given character or stringReturns the portion of text after the specified delimiter.
Syntax:TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

Text.AfterDelimiter(text, delimiter, [index])
Example:=TEXTAFTER(“Example text”,” “)=Text.AfterDelimiter(“Example text”, ” “)
Result:“text”“text”

Notes:

  • Power Query uses a zero-base. Therefore, [index] of 0 is equivalent to [instance_num] of 1.
  • Both functions provide optional arguments to start at the end of the string.
  • [match_mode] determines whether the delimiter search is case sensitive. (0 = Case sensitive, 1 = Case insensitive). Power Query is case sensitive.
  • [match_end] treats the end of the text as a delimiter. Power Query does not have an equivalent argument.
  • [if_not_found] is the value to return if no matching delimiter is found. Power Query does not have an equivalent argument.
  • Power Query has the Text.BetweenDelimiters function to provide the text between two delimiters. In Excel, this can be created through a combination of TEXTAFTER and TEXTBEFORE.

TEXTBEFORE

ExcelPower Query
Name:TEXTBEFOREText.BeforeDelimiter
Description:Returns text that occurs before given character or stringReturns the portion of text before the specified delimiter.
Syntax:TEXTBEFORE(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])

Text.BeforeDelimiter(text, delimiter [index])
Example: =TEXTBEFORE(“Example text”,” “)=Text.BeforeDelimiter(“Example text”, ” “)
Result:“Example”“Example”

Notes:

  • See TEXTAFTER for similar examples and usage notes.

TEXTJOIN

ExcelPower Query
Name:TEXTJOINText.Combine
Description:Combines the text from multiple ranges and/or strings.Returns a text value that is the result of joining all text values with each value separated by a separator.
Syntax:TEXTJOIN(delimiter, ignore_empty, text1, [text2]…)Text.Combine(texts, [separator])
Example:=TEXTJOIN(“,”, FALSE, “Example”, “text”)=Text.Combine({“Example”, “text”}, “,”)
Result:“Example,text”“Example,text”
Notes:
  • The CONCAT function combines text without using a separator.

TRIM

ExcelPower Query
Name:TRIMText.Trim
Description:Removes spaces from text.Returns the result of removing all leading and trailing whitespace from text value.
Syntax:TRIM(text)Text.Trim(text, [trim])
Example:=TRIM(” Example text “)=Text.Trim(” Example text “)
Result:“Example Text”“Example text”
Notes:
  • The Power Query version of Trim does not remove white space from the middle of text values. Check out this post to replicate Excel’s TRIM function in Power Query.
  • Text.Trim includes an optional [trim] argument to enable trimming of other characters.
    For example, to trim hyphens use “-” as the [trim] argument.
    Example: Text.Trim( “—Example Text—“, “-” )
    Result: “Example Text”
  • Power Query has additional trim functions:
    • Text.TrimEnd – Returns the result of removing all trailing whitespace from text value
      Syntax: Text.TrimEnd (text, [trim])
    • Text.TrimStart – Returns the result of removing all leading whitespace from text value
      Syntax: Text.TrimStart (text, [trim])

UPPER

ExcelPower Query
Name:UPPERText.Upper
Description:Converts text to uppercase.Returns the uppercase of a text value.
Syntax:UPPER(text)Text.Upper(text, [culture])
Example:=UPPER(“Example text”)=Text.Upper(“Example text”)
Result:“EXAMPLE TEXT”“EXAMPLE TEXT”

Notes:

  • The optional [culture] argument within Text.Upper is not covered in this post.

Conclusion

For the majority of Excel text functions, there is an equivalent Power Query text function.

However, there are important differences to be aware of:

  • Many Power Query arguments and results are zero-based.
  • A function may have additional arguments which are not available in the equivalent function.
  • The order of arguments may differ between
  • Power Query, by default, is case-sensitive, Excel is case-insensitive.

Want to know more about Power Query text functions? Check out this post on Gorilla.bi

Related Posts:

4 thoughts on “Text functions – Excel & Power Query comparision”

  1. You should change this:

    “CONCAT (introduced in Excel 2019) is the replacement for CONCATENATE.”

    to this:

    “CONCAT (introduced in Excel 2019) is a more flexible alternative to CONCATENATE.”

    Reply
    • Microsoft’s guidance says this: “CONCAT replaces the CONCATENATE function. However, the CONCATENATE function will stay available for compatibility with earlier versions of Excel.”

      So, it seems is meant to be a replacement.

      But I you’re right about it being a more flexible alternative. I’ll update now.

      Reply

Leave a Comment