Text.Insert

D

Back to top

T

Back to top

#

Back to top

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

Syntax of Text.Insert Function

The syntax of the Text.Insert function is as follows:

Text.Insert(text as nullable text, offset as number, insert as text) as nullable text

Here, `text` is the text in which you want to insert the substring, `offset` is the index at which you want to insert the substring, and `insert` is the substring that you want to insert.

Understanding the M Code Behind Text.Insert

To understand the M code behind the Text.Insert function, let’s take a look at an example. Suppose we have the following table in Power Query:

| Name | Age | Gender |

|-------|-----|--------|

| John | 30 | Male |

| Emily | 25 | Female |

Now, we want to insert the word “is” after the word “Name” in the column headers. To do this, we can use the following M code:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtY0sgvK1UvOz0lV0lFyKzOzUwvOyC9JzE0uSS0q0gpKSixJzU0tSrVTS1WwMgA", BinaryEncoding.Base64)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Name" = _t, Age = _t, Gender = _t])),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Age", Int64.Type}, {"Gender", type text}}),

#"Inserted Text" = Table.AddColumn(#"Changed Type", "Name with Is", each Text.Insert([Name], 5, "is "))

in

#"Inserted Text"

In this code, we first create a table named “Source” with the given data. Then, we transform the column type of the “Age” column to integer. After that, we add a new column named “Name with Is” using the Text.Insert function. Here, we insert the substring “is ” after the 5th index of the “Name” column.

Using Text.Insert in Different Scenarios

Now that we know the M code behind the Text.Insert function, let’s explore some scenarios where we can use it.

Scenario 1: Inserting a Substring in a Text Column

Suppose we have a table with a text column named “Description”. We want to insert a substring “is important” after the word “data” in all the rows of the “Description” column. To do this, we can use the following M code:

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

#"Inserted Text" = Table.AddColumn(Source, "New Description", each Text.Insert([Description], Text.PositionOf([Description], "data") + 4, " is important"))

in

#"Inserted Text"

In this code, we first load the data from the “Table1” table. Then, we add a new column named “New Description” using the Text.Insert function. Here, we find the index of the word “data” using the Text.PositionOf function and add 4 to it (to skip the length of the word “data”). Finally, we insert the substring ” is important” at this index.

Scenario 2: Inserting a Substring in a Date Column

Suppose we have a table with a date column named “Date”. We want to insert the string ” (Sunday)” after the date if it falls on a Sunday. To do this, we can use the following M code:

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

#"Inserted Text" = Table.AddColumn(Source, "New Date", each if Date.DayOfWeek([Date]) = Day.Sunday then Text.Insert(Text.From([Date]), 11, " (Sunday)") else Text.From([Date]))

in

#"Inserted Text"

In this code, we first load the data from the “Table1” table. Then, we add a new column named “New Date” using the Text.Insert function. Here, we check if the day of the week of the date is Sunday using the Date.DayOfWeek function. If it is Sunday, we insert the string ” (Sunday)” after the date using the Text.Insert function. Otherwise, we simply convert the date to text using the Text.From function.

Text.Insert is a powerful M function in Power Query that can save you a lot of time if you know how to use it effectively. In this article, we explored the M code behind the Text.Insert function and learned how to use it in different scenarios. By using Text.Insert, you can easily insert a substring in a given text at a specified index and transform your data 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)