Text.Select - Text Function

Text.Select is a Power Query M function that removes all characters from a text value that are not in a specified list. The function returns the modified text value with only the specified characters remaining.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Text.Select(
   text as nullable text,
   selectChars as any,
) as nullable text

Description

The Text.Select function is a powerful function for extracting specific characters from a text string. This function allows you to selectively choose characters based on various criteria, such as lowercase or uppercase letters, numbers, symbols, or a combination of these.

It returns a copy of the text value text with all the characters not in selectChars removed.

Examples

In this chapter, we will explore several real-life examples to demonstrate the functionality of Text.Select.

Extracting Lowercase Letters

Suppose you have a text string “a1b2c3d4” and you want to extract only the lowercase letters. By using the Text.Select function with the character range from ‘a’ to ‘z’, you can achieve this effortlessly. The resulting value would be “abcd,” containing all the lowercase characters present in the original string.

Text.Select( "a1b2c3d4", { "a".."z" } ) // Returns "abcd"

Extracting Uppercase Letters

Similarly, if you need to retrieve only the uppercase letters from a given text string, Text.Select comes to the rescue. For instance, consider the string “This is a GREAT summer.” Applying Text.Select with the character range from ‘A’ to ‘Z’ will provide you with “TGREAT,” which consists of all the uppercase letters in the original text.

Text.Select( "This is a GREAT summer", { "A".."Z" } ) // Returns "TGREAT"

Extracting Numeric Values

Suppose you have a column containing strings representing prices in various formats, such as “Price: $1,234.56”. To extract the numeric value, you can use Text.Select with the character range of numbers (0-9) and the decimal separator. This will yield “1234.56,” which can be incredibly helpful when you need to perform numerical calculations or analysis on price data.

Text.Select("Price: $1,234.56", { "0".."9", "." } ) // Returns "1234.56"

Extracting Numbers from Postal Codes

If you have a dataset containing postal codes, extracting only the numeric values can be useful. For instance, consider the postal code “3874BT”. By applying Text.Select with the character range of numbers (0-9), you can effortlessly obtain “3874,” which represents the numeric portion of the postal code.

Text.Select( "3874BT", { "0".."9", "." } ) // Returns "3874"

Extracting Numbers from Invoice Numbers

In the context of managing invoices, it can be beneficial to extract only the numbers from an invoice number string. Suppose you have the string “Invoice #12345”. By employing Text.Select with the character range of numbers (0-9), you can extract “12345,” allowing for easier processing and analysis of invoice data.

Text.Select( "Invoice #12345", {"0".."9"} ) // Returns "12345"

Extracting Numbers from ISBNs

Text.Select can also be useful when dealing with ISBN numbers in a string format. Consider the string “ISBN: 978-3-16-148410-0”. By using Text.Select with the character range of numbers (0-9), you can effortlessly retrieve the ISBN number “978-3-16-148410-0,” making it easier to process and manage book-related information.

Text.Select( "ISBN: 978-3-16-148410-0", {"0".."9", "-"} ) // Returns "978-3-16-148410-0"

Learn more about Text.Select in the following articles:

Text Functions in Power Query M (150+ Examples)
Your guide to Text Functions in Power Query M. Learn from practical examples and master Power Query’s most useful Text functions. » Read more

Other functions related to Text.Select are:

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