Text Functions Overview

Updated on

Understand Text Functions in Power Query M Language, crucial for creating and manipulating textual data. This page provides a comprehensive list and descriptions of Text Functions available in the M Language.

Table of contents

Conversion and Creation Functions

FunctionDescriptionInputOutput
Character.FromNumberConverts the number to its character value.NumberText
Character.ToNumberConverts a character to its number value.TextNumber
Guid.FromReturns a guid value from the given value.TextText
Text.FromCreates a text value from the given value.AnyText
Text.FromBinaryDecodes data from a binary form into text.BinaryText
Text.NewGuidReturns a new, random globally unique identifier (GUID).TextText
Text.ToBinaryEncodes text into a binary form.TextBinary
Text.ToListReturns a list of character values from the given text value.TextList

Extraction and Position Functions

FunctionDescriptionInputOutput
Text.AfterDelimiterReturns the portion of text after the specified delimiterTextAny
Text.AtReturns the character at the specified position.TextText
Text.BeforeDelimiterReturns the portion of text before the specified delimiterTextAny
Text.BetweenDelimitersReturns the portion of text between the specified startDelimiter and endDelimiterTextAny
Text.EndReturns the last characters of the text.TextText
Text.MiddleReturns the substring up to a specific length.TextText
Text.PositionOfReturns the first position of the value (-1 if not found).TextAny
Text.PositionOfAnyReturns the first position in the text value of any listed character (-1 if not found).TextAny
Text.RangeReturns the substring found at offset.TextText
Text.SelectSelects all occurrences of the given character or list of characters from the input text value.TextText
Text.StartReturns the start of the text.TextText
Text.TrimEndRemoves all trailing whitespace.TextText
Text.TrimStartRemoves all leading whitespace.TextText

Formatting Functions

FunctionDescriptionInputOutput
Text.CleanReturns the text value with all control characters removed.TextText
Text.FormatReturns formatted text from a format string and arguments.TextText
Text.LowerConverts all characters to lowercase.TextText
Text.ProperCapitalizes the first letter of each word.TextText
Text.TrimRemoves all leading and trailing whitespace.TextText
Text.UpperConverts all characters to uppercase.TextText

Information Functions

FunctionDescriptionInputOutput
Text.ContainsReturns whether the text contains the substring.TextLogical
Text.EndsWithIndicates whether the text ends in the specified value.TextLogical
Text.InferNumberTypeInfers the granular number type (Int64.Type, Double.Type, and so on) of a number encoded in text.TextType
Text.LengthReturns the number of characters.TextNumber
Text.StartsWithIndicates whether the text starts with a specified value.TextLogical

Transformation Functions

FunctionDescriptionInputOutput
Text.CombineConcatenates a list of text values into one text value.ListText
Text.InsertInserts one text value into another at a given position.TextText
Text.PadEndReturns text of a specified length by padding the end of the given text.TextText
Text.PadStartReturns text of a specified length by padding the start of the given text.TextText
Text.RemoveRemoves all occurrences of the given character or list of characters from the input text value.TextText
Text.RemoveRangeRemoves a count of characters starting at the given offsetTextText
Text.RepeatReturns a text value composed of the input text repeated a specified number of times.TextText
Text.ReplaceReplaces all occurrences of the given substring in the text.TextText
Text.ReplaceRangeRemoves a range of characters and inserts a new value at a specified position.TextText
Text.ReverseReverses the provided textTextText
Text.SplitSplits text into a list of text values based upon a specified delimiter.TextList
Text.SplitAnyReturns a list of text values, split on any of the characters in the delimiter.TextList

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/text-functions