Text.Contains

In this article, we will explore the M code behind the Text.Contains function and how it can be used to perform complex text matching operations in Power Query.

Overview of Text.Contains Function

The Text.Contains function in Power Query takes two arguments: the text to search and the substring to search for. It returns a Boolean value of true if the substring is found in the text, and false otherwise.

The syntax for the Text.Contains function is as follows:

The first argument, `text`, is the text to search. It can be a column in a data table, a variable, or a literal string.

The second argument, `substring`, is the substring to search for. It can also be a column, a variable, or a literal string.

The optional third argument, `comparer`, is a function that specifies how the comparison should be made. It can be used to perform case-insensitive or culture-specific comparisons.

The M Code Behind Text.Contains

The M code behind the Text.Contains function is relatively simple, as it consists of just a few lines of code. Here’s an example of how the function can be implemented in M code:

The code defines a function that takes the same three arguments as the Text.Contains function. It then checks if either the `text` or `substring` arguments are null, in which case it returns null.

If the `comparer` argument is not null, the function uses the comparer function to perform the comparison. Otherwise, it uses the built-in Text.Contains function to perform a case-sensitive comparison.

Examples of Text.Contains Function Usage

Here are some examples of how the Text.Contains function can be used in Power Query:

Example 1: Basic Text Matching

Suppose we have a data table containing a column of product names, and we want to filter the table to only include products that contain the word “apple”. We can use the Text.Contains function to achieve this as follows:

This code filters the `Products` table to only include rows where the `Product Name` column contains the substring “apple”.

Example 2: Case-Insensitive Text Matching

Suppose we have a column of email addresses in a data table, and we want to filter the table to only include rows where the email address contains the domain “gmail.com”, regardless of case. We can use the Text.Contains function with a case-insensitive comparer as follows:

This code filters the `Emails` table to only include rows where the `Email Address` column contains the substring “gmail.com”, ignoring case.

Example 3: Culture-Specific Text Matching

Suppose we have a column of names in a data table and we want to filter the table to only include rows where the name contains the letter “é”. We can use the Text.Contains function with a culture-specific comparer as follows:

This code filters the `Names` table to only include rows where the `Name` column contains the substring “é”, using the French culture for the comparison.

The Text.Contains function in Power Query is a powerful tool for text matching and filtering operations. By understanding the M code behind the function and its optional arguments, you can perform complex text matching operations with ease and flexibility.

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

Upcoming Courses

Contact Us