Text.Insert

Updated on

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

Returns the result of inserting text value newText into the text value text at position offset. Positions start at number 0.

Examples

Text.Insert is a great function to attach text to the end of your word. Here’s how you insert “h” after “Arc”.

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

To add some text to the start of a value, you can use the position index of 0. Add the text “Big ” in front of “Fish” as follows.

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

If you want to add a particular value to the end of a word you can combine Text.Insert with the Text.Length function. Add the letter “s” at the end of the word “chair”.

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

/* -- -- similarly you can make this dynamic. Suffix words in the [YourString] column
   -- --  by combining Text.Insert with Text.Length */

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

Learn more about Text.Insert in the following articles:

BI Gorilla Youtube Channel

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