Text.Insert - Text Function

Text.Insert is a Power Query M function that inserts a new text value into an existing text value at a specified position. It returns the modified text value enriched with the new text.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Description

The Text.Insert function returns the result of inserting text value newText into the text value text at position offset. Positions start at number 0.

Examples

The Text.Insert function is useful for adding text at specific positions within a string. Let’s explore how it works with some examples.

Inserting Text at the End

To insert “h” after “Arc” to form “Arch”, you can use the following code:

Text.Insert( "Arc", 3, "h" ) // Returns "Arch"

Adding Text to the Start

To add text to the beginning of a string, use a position index of 0. For example, to add “Big ” in front of “Fish”:

Text.Insert( "Fish", 0, "Big " ) // Returns "Big Fish"

Adding Text to the End

If you want to add a particular value to the end of a word, you can combine Text.Insert with the Text.Length function. For example, to add the letter “s” at the end of the word “Chair”:

Text.Insert( "Chair", Text.Length( "Chair"), "s" ) // Returns "Chairs"

Dynamic Suffix Addition

You can also make this dynamic by appending a suffix to words in the [YourString] column. Here’s how you can combine Text.Insert with Text.Length to achieve this:

Text.Insert(
   [YourString],
   Text.Length( [YourString] ), // Retrieves the length of [YourString]
   "s"
)

Learn more about Text.Insert in the following articles:

Text Functions in Power Query M (150+ Examples)
Your guide to Text Functions in Power Query M. Learn from practical examples and master Power Query’s most useful Text functions. » Read more

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/text-insert