Text.Insert Power Query Function

Text.insert power query function helps modify the given string. Sometimes, we need to add a wildcard character at the start, at the end, or at both ends to retrieve data from different sources.

Syntax

The syntax of Text.insert function is

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

It takes three parameters.

• The first parameter is the given string that we need to modify.
• The second parameter is the position index, where we have to add any character.
• The third parameter is the character that we have to add.

Text.insert Power query function example.

Let’s make a scenario where we need to fetch available months’ data from our database. Unfortunately, there is an issue with the data. Some months have the full name, and others have the first three characters, as you can see below.

We need to add “*” at the beginning and end of each month so that we don’t miss any data from our database.

Let’s try to achieve it by using the following formula.

Text.Insert([Months],0,"*")

We will get the following results.

We have added the “*” in the beginning, but now we need to add the “*” at the end. 

How can we do that? 

Let’s break it into the following pieces.

• Count the number of characters in the string 
• Use the above count or length to add “*” at the end

Text.Length Power query function

We use Text.length function to calculate the number of characters in the given string. It works like the “Count” function in other programming languages, especially SQL.

Syntax of Text.length

Text.Length(Given string)

It takes one parameter as a string and returns the number of characters.

Let’s move to our original problem and try to calculate the length of the string by using the following formula.

Text.Length([Modified month])

• Go to the power query editor 
• Add column and the custom column

Now we have the total length we can use in Text.insert power query function as a position to insert “*” at the end.

Let’s write the following formula to get our final output.

Text.Insert([Modified month],[Custom],"*")

[Custom] is the column’s name that contains the length of different months.

By applying the above formula, we will get the following desired output.

In this article, you have learned how powerful is Text.insert function is together with the Text.length function.

To learn more, please visit Learn DAX