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 textDescription
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.
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"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"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"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"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"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"Related articles
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
Related functions
Other functions related to Text.Select are:
