Text.PositionOf

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Text.PositionOf

What is Text.PositionOf?

The Text.PositionOf function is used to find the position of a substring in the given text. The syntax for this function is as follows:

Text.PositionOf(text as nullable text, substring as text, optional occurrence as nullable number) as nullable number

In this syntax, the first argument ‘text’ is the text in which we want to find the position of the substring. The second argument ‘substring’ is the text that we want to find in the given text. The third argument ‘occurrence’ is an optional argument that specifies the occurrence of the substring that we want to find. If the occurrence is not specified, the function returns the position of the first occurrence of the substring.

The M code behind Text.PositionOf

When we use the Text.PositionOf function in Power Query, it generates the following M code:

(text as nullable text, substring as text, optional occurrence as nullable number) =>

let

pos = if occurrence = null then Text.PositionOf(text, substring) else Text.PositionOf(text, substring, occurrence)

in

pos

This M code can be divided into two parts – the function definition and the function body.

Function definition

The function definition specifies the arguments of the function and their respective data types. In the case of Text.PositionOf, the function definition is as follows:

(text as nullable text, substring as text, optional occurrence as nullable number)

This function definition specifies that there are three arguments – ‘text’, ‘substring’, and ‘occurrence’. The ‘text’ and ‘substring’ arguments are of type ‘text’, while the ‘occurrence’ argument is of type ‘nullable number’.

Function body

The function body is the code that is executed when the function is called. In the case of Text.PositionOf, the function body is as follows:

let

pos = if occurrence = null then Text.PositionOf(text, substring) else Text.PositionOf(text, substring, occurrence)

in

pos

This function body starts with the ‘let’ keyword, which is used to define a new local variable, ‘pos’. The ‘if’ statement checks whether the ‘occurrence’ argument is null or not. If it is null, the function calls the Text.PositionOf function with two arguments – ‘text’ and ‘substring’. If it is not null, the function calls the Text.PositionOf function with three arguments – ‘text’, ‘substring’, and ‘occurrence’. The result of the Text.PositionOf function is assigned to the ‘pos’ variable.

Finally, the ‘in’ keyword is used to return the value of the ‘pos’ variable as the result of the function.

Examples

Let’s look at some examples of how to use the Text.PositionOf function in Power Query.

Example 1

Suppose we have the following table:

| ID | Name |

|—-|————–|

| 1 | John Smith |

| 2 | Jane Doe |

| 3 | Mary Johnson|

We want to find the position of the substring ‘Smith’ in the ‘Name’ column. We can use the following code:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjIwMjLVMlEyNjQ0VrIzNQPyALC0q1ZyRilzyrWjPz00tNQzLrYx2clPz8hPSy1K1Y0MqyMz81MjE0MDZRMjQwSjFxTcxKLCkFkqSeTlWpYBgXVQ1cA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t]),

#"Added Custom" = Table.AddColumn(Source, "Position", each Text.PositionOf([Name], "Smith"))

in

#"Added Custom"

This code adds a new column called ‘Position’ to the table, which contains the position of the substring ‘Smith’ in the ‘Name’ column.

Example 2

Suppose we have the following text:

The quick brown fox jumps over the lazy dog.

We want to find the position of the substring ‘fox’ in the given text. We can use the following code:

let

Source = "The quick brown fox jumps over the lazy dog.",

Position = Text.PositionOf(Source, "fox")

in

Position

This code returns the position of the substring ‘fox’ in the given text, which is 17.

In this article, we explored the M code behind the Text.PositionOf function in Power Query. We learned that the function is used to find the position of a substring in a given text. We also learned that the function definition specifies the arguments of the function and their respective data types, while the function body is the code that is executed when the function is called. Finally, we looked at some examples of how to use the Text.PositionOf function in Power Query.

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)