List.FindText

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function List.FindText

What is the List.FindText Function?

The List.FindText function is used to search for a specific text string within a list of values. It returns the index of the first item in the list that contains the specified text. This function is particularly useful when working with large datasets where manual searching is not practical.

Syntax

The syntax for the List.FindText function is as follows:

List.FindText(list as list, searchText as text, optional occurrence as nullable number) as nullable number

- list - The list of values to search for the specified text.

- searchText - The text to search for within the list of values.

- occurrence - (optional) The occurrence of the specified text to search for. Default value is 1.

M Code Explanation

The List.FindText function is comprised of several steps that work together to search for the specified text within a list of values. Let's break down the M code behind this function:

let

Source = (list as list, searchText as text, optional occurrence as nullable number) =>

let

ListLength = List.Count(list),

OccurrenceCount = List.Generate(

() => [Count = 0, Index = -1],

each [Count] < ListLength,

each [Count = [Count] + 1, Index = List.FindText(List.Skip(list, [Index] + 1), searchText) + [Index] + 1],

each [Index]

),

OccurrenceIndex = List.Skip(OccurrenceCount, occurrence – 1){0}

in

if OccurrenceIndex = ListLength then null else OccurrenceIndex

in

Source

Step 1: Function Parameters

The first step in the M code behind the List.FindText function is to define the function parameters. These parameters are list, searchText, and occurrence (optional).

(list as list, searchText as text, optional occurrence as nullable number) =>

Step 2: List Length

The next step is to determine the length of the input list using the List.Count function.

ListLength = List.Count(list),

Step 3: Occurrence Count

The Occurrence Count step is used to generate a list of occurrence indexes for the specified text within the input list. This step uses the List.Generate function to iterate over each item in the input list and find the next occurrence of the specified text.

OccurrenceCount = List.Generate(

() => [Count = 0, Index = -1],

each [Count] < ListLength,

each [Count = [Count] + 1, Index = List.FindText(List.Skip(list, [Index] + 1), searchText) + [Index] + 1],

each [Index]

),

Step 4: Occurrence Index

The Occurrence Index step is used to retrieve the specified occurrence index from the list generated in step 3. This step uses the List.Skip function to skip over the previous occurrence indexes and retrieve the specified occurrence index.

OccurrenceIndex = List.Skip(OccurrenceCount, occurrence – 1){0}

Step 5: Return Value

The final step is to return the occurrence index if it exists, or null if it does not.

if OccurrenceIndex = ListLength then null else OccurrenceIndex

Example Usage

Let's look at an example of how the List.FindText function can be used to enhance data analysis. Suppose we have a table of customer data that includes a column for customer email addresses. We want to search for all customers who have a Gmail email address.

We can use the List.FindText function in Power Query to create a custom column that flags customers with a Gmail email address. Here's how we can do it:

1. Load the customer data into Power Query.

2. Click on the "Add Column" tab and select "Custom Column".

3. In the "Custom Column" dialog box, enter a name for the new column (e.g. "Gmail").

4. In the "Custom Column" dialog box, enter the following formula:

= List.FindText(Text.Split([Email], “@”)[1], “gmail”)

5. Click "OK" to create the new column.

This formula splits the email address into two parts (username and domain) using the Text.Split function. It then searches for the text "gmail" within the domain using the List.FindText function. If the text is found, the function returns the index of the first item in the list that contains the text. If the text is not found, the function returns null.

The resulting custom column will contain the index of the first occurrence of "gmail" in the domain of the email address for each customer. We can filter the table to show only customers with a non-null value in this column to see all customers with a Gmail email address.

The List.FindText function is a powerful tool for searching for specific text within a list of values in Power Query. By understanding the M code behind this function, we can use it to enhance our data analysis and make more informed decisions.

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)