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"
)
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
2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy