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 textDescription
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"
)Related articles
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
