Text.Insert Function in Power Query M (4 Easy Examples)

If you want to drop a piece of text into the middle of a string, the Text.Insert function does exactly that. It slides newText into your original string at a position you pick, pushing the rest of the characters to the right.

Table of Contents

Syntax of Text.Insert Function

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

where

text (required, nullable text). The original string you want to insert into. It can be null.
offset (required, number). The 0-based position where newText goes. Position counting starts at 0.
newText (required, text). The string you want to insert at offset.

Returns: a text value with newText inserted at offset. If text is null, it returns null.

So Text.Insert takes your string, finds the spot you name with offset, and wedges the new text in there. Nothing gets removed. The characters that were already there just shift over to make room.

Example 1 – Insert text in the middle of a string

Say you have the string ABCDEF and you want a dash to land right after the third character.

let
    Source = Text.Insert("ABCDEF",3,"-")
in
    Source

Result: ABC-DEF

The offset of 3 means the dash is inserted before the character currently sitting at index 3 (which is D). So it lands between C and D, and everything from D onward shifts right.

Example 2 – Prepend text with offset 0

When offset is 0, the new text goes in front of everything. This is how you prepend a prefix. If you instead need to pad the front to a fixed width, Text.PadStart is the better fit.

let
    Source = Text.Insert("Sheet",0,"Spread")
in
    Source

Result: SpreadSheet

Since offset is 0, Spread is inserted before the first character of Sheet. The result reads SpreadSheet.

Example 3 – Append text at the end

To stick text on the end, set offset equal to the length of the string. Using Text.Length keeps it dynamic, so it works no matter how long the string is.

let
    Source = Text.Insert("INV2024",Text.Length("INV2024"),"-A")
in
    Source

Result: INV2024-A

Text.Length("INV2024") is 7, which points just past the last character. So -A gets tacked on at the very end.

Example 4 – Insert a separator into a column of codes

You can run Text.Insert down a whole column to reformat each value. Here is a table named Codes with location codes that need a dash after the airport prefix.

Code
NYC2024
LAX2023
SFO2022

This query uses Table.AddColumn to add a Formatted column that inserts a dash at offset 3 in each code.

let
    Source = Excel.CurrentWorkbook(){[Name="Codes"]}[Content],
    Added = Table.AddColumn(Source,"Formatted",each Text.Insert([Code],3,"-"),type text)
in
    Added

Code	Formatted
NYC2024	NYC-2024
LAX2023	LAX-2023
SFO2022	SFO-2022

The dash lands after the three-letter prefix in every row, splitting the code into a readable prefix-year shape.

Things to keep in mind with Text.Insert

The offset is 0-based, not 1-based. Position N inserts before the character currently at index N, so offset 3 in ABCDEF puts the new text between C and D.
An offset of 0 prepends and an offset equal to Text.Length(text) appends. Use Text.Length([Col]) when you want a dynamic append on a column.
An out-of-range offset (bigger than the string length, or negative) throws an Expression.Error. It does not clamp or quietly append, so guard fixed-width assumptions when a column might be shorter than you expect.
A null text value returns null rather than erroring. A null row passes straight through, so it will not break the step.
Text.Insert only inserts. It pushes the existing characters right and never overwrites them. To overwrite a span instead, reach for Text.ReplaceRange.

Common questions about Text.Insert

What is the difference between Text.Insert and Text.ReplaceRange?

Text.Insert adds characters without removing any, so the string gets longer. Text.ReplaceRange deletes a span of characters and drops new text in its place. If you want to overwrite part of a string, use Text.ReplaceRange. Text.Insert is basically Text.ReplaceRange with a replace count of 0. And to swap out whole cell values across a column, Table.ReplaceValue is the tool to reach for.

How do I append text instead of inserting in the middle?

Set the offset to the length of the string with Text.Length. For a column, write Text.Insert([Col],Text.Length([Col]),"suffix") and the suffix lands at the end of every value.

List of All Power Query Functions

Related Power Query Functions / Articles: