Text.Range

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Text.Range

Anatomy of the Text.Range Function

The Text.Range function takes three arguments: the text value, the starting position of the substring, and the length of the substring. Here is the basic syntax of the Text.Range function:

Text.Range(text as text, start as number, count as number) as text

– `text` is the text value from which you want to extract a substring.

– `start` is the starting position of the substring. The first character in the text value has a position of 0.

– `count` is the length of the substring.

For example, the following formula extracts the first three characters from the text value “Hello, World!”:

Text.Range("Hello, World!", 0, 3)

The result is “Hel”.

Advanced Features of the Text.Range Function

The Text.Range function has some advanced features that allow you to extract substrings based on more complex criteria.

Extracting a Substring based on a Delimiter

Often, you may want to extract a substring based on a delimiter, such as a comma or a space. You can achieve this by combining the Text.Range function with the Text.PositionOf function, which returns the position of a substring within a text value.

For example, the following formula extracts the first word from the text value “Hello, World!”:

Text.Range("Hello, World!", 0, Text.PositionOf("Hello, World!", ","))

The result is “Hello”.

Extracting a Substring based on a Pattern

You can also extract a substring based on a pattern using the Text.Range function. For example, you may want to extract all the digits from a text value. You can achieve this by combining the Text.Range function with the Text.PositionOfAny function, which returns the position of the first occurrence of any of a set of characters within a text value.

Here is an example formula that extracts all the digits from the text value “123-456-7890”:

Text.Range("123-456-7890", Text.PositionOfAny("123-456-7890", {"0","1","2","3","4","5","6","7","8","9"}), Text.Length("123-456-7890") - Text.PositionOfAny("123-456-7890", {"0","1","2","3","4","5","6","7","8","9"}))

The result is “1234567890”.

Extracting a Substring based on a Regular Expression

Finally, you can extract a substring based on a regular expression using the Text.Range function. A regular expression is a pattern that matches one or more characters in a text value.

To extract a substring based on a regular expression, you can use the Text.RegularExpressions function, which returns a list of matches for a regular expression within a text value. You can then extract the desired substring from the list using the Text.Range function.

Here is an example formula that extracts all the email addresses from the text value “john@example.com, jane@example.com”:

let

text = "john@example.com, jane@example.com",

matches = Text.RegularExpressions(text, "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,}")

in

Text.Range(matches{0}, 0, Text.Length(matches{0}))

The result is “john@example.com”.

The Text.Range function is a powerful tool for extracting substrings from text values in Power Query. By combining the Text.Range function with other functions, such as Text.PositionOf, Text.PositionOfAny, and Text.RegularExpressions, you can extract substrings based on more complex criteria. With these advanced features, you can handle a wide range of data transformation tasks in Power Query.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)