Table.FindText

D

Back to top

T

Back to top

#

Back to top

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

What is the M language?

Before we dive into the code, it’s important to understand what the M language is. M is the programming language used to create custom functions and queries in Power Query. It’s a functional language, meaning that it’s based on the concept of functions and expressions.

Syntax of Table.FindText

Let’s take a look at the syntax of the Table.FindText function:

Table.FindText(table as table, searchCriteria as text, optional occurrence as nullable number) as nullable number

The function takes three arguments:

1. `table`: The table to search within.

2. `searchCriteria`: The text to search for.

3. `optional occurrence`: The occurrence number of the text to find.

The function returns the position of the first occurrence of the text in the table, or null if the text is not found.

The M code behind Table.FindText

The M code behind Table.FindText is relatively simple. Here is the code:

(table as table, searchCriteria as text, optional occurrence as nullable number) =>

let

tableHeaders = Table.ColumnNames(table),

searchColumns = List.Select(tableHeaders, each Type.Is(Text.Type, Table.ColumnType(table, _))),

foundRows = Table.SelectRows(table, each List.Contains(List.Transform(searchColumns, each Record.Field(_, searchCriteria)), true)),

result = if List.IsEmpty(foundRows) then null else Table.PositionOf(foundRows, foundRows{0}),

finalResult = if occurrence = null then result else List.PositionOf(List.FirstN(Table.Column(foundRows, result){0}, occurrence), true)

in

finalResult

Let’s break down what’s happening in this code.

Step 1: Define the function arguments

The first line of the code defines the function arguments:

(table as table, searchCriteria as text, optional occurrence as nullable number) =>

This line tells Power Query that the function takes three arguments: `table`, `searchCriteria`, and `occurrence`.

Step 2: Get the table headers and search columns

The next line of the code gets the table headers and search columns:

tableHeaders = Table.ColumnNames(table),

searchColumns = List.Select(tableHeaders, each Type.Is(Text.Type, Table.ColumnType(table, _))),

This code gets the names of all the columns in the table, and then selects only the columns that are of type “text”.

Step 3: Search for the text

The next line of the code searches for the text:

foundRows = Table.SelectRows(table, each List.Contains(List.Transform(searchColumns, each Record.Field(_, searchCriteria)), true)),

This code selects all the rows in the table where the search criteria is found within the search columns.

Step 4: Get the position of the text

The next line of the code gets the position of the text:

result = if List.IsEmpty(foundRows) then null else Table.PositionOf(foundRows, foundRows{0}),

This code checks if any rows were found, and if so, gets the position of the first row where the search criteria was found.

Step 5: Get the occurrence number

The final step of the code gets the occurrence number:

finalResult = if occurrence = null then result else List.PositionOf(List.FirstN(Table.Column(foundRows, result){0}, occurrence), true)

This code checks if an occurrence number was provided, and if so, gets the position of the text within that occurrence number.

The Table.FindText function is a powerful tool in Power Query that allows you to search for specific text within a table. Understanding the M code behind this function can help you customize it to suit your specific needs. By knowing how the function works, you can create more complex queries and functions that can transform and analyze your data in new and innovative ways.

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)