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

Text.Start returns the first count characters from the start of a text value. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to pull the leading characters out of a text value, like a prefix or a code, this is the function you reach for.

Table of Contents

Syntax of Text.Start Function

Text.Start(text as nullable text, count as number) as nullable text

where

text (required, nullable text). The source text value you want to take characters from.
count (required, number). How many characters to take from the start (the left side).

Returns: a text value made of the first count characters of text. If text is null, it returns null.

In plain terms, you give it some text and a number, and it hands back that many characters counted from the left.

Example 1: Get the first few characters of a value

Take the first 6 characters of the word Spreadsheet.

let
    Source = Text.Start("Spreadsheet",6)
in
    Source

Result: Spread

Counting 6 characters from the left of Spreadsheet gives you Spread.

Example 2: A count larger than the text returns the whole string

This is the behavior most people trip on. If count is bigger than the length of the text, you get the whole string back with no error.

let
    Source = Text.Start("Excel",20)
in
    Source

Result: Excel

Excel is only 5 characters long, so asking for 20 just returns all of it. That makes Text.Start handy when you want the first few characters but the values vary in length and you don’t want it to break on the short ones.

Example 3: A count of zero returns empty text

Passing 0 for count returns an empty text value rather than an error.

let
    Source = Text.Start("Planet",0)
in
    Source

Result: "" (empty text)

You asked for zero characters, so you get an empty string back.

Example 4: Pull a category code from a SKU column

The most common real use is grabbing a fixed-length prefix out of a column.

Say you have a Products table with a SKU column, and each SKU starts with a 4-letter category code.

You want a new Category column holding just those first 4 characters.

Here is the starting data:

SKU
ELEC-1001
HOME-2204
TOYS-3380
GARD-4517

Add a column with Table.AddColumn that takes the first 4 characters of each SKU:

let
    Source = Excel.CurrentWorkbook(){[Name="Products"]}[Content],
    Result = Table.AddColumn(Source,"Category",each Text.Start([SKU],4),type text)
in
    Result

This runs Text.Start on every row of the SKU column.

The result adds the Category column:

SKU	Category
ELEC-1001	ELEC
HOME-2204	HOME
TOYS-3380	TOYS
GARD-4517	GARD

Each category code is the first 4 characters of its SKU. If your source values have stray spaces, run Text.Trim first so the count lands on the right characters.

Things to keep in mind with Text.Start

A count larger than the text length returns the whole string, no error. It caps at the string length instead of throwing (Example 2), so it is safe on values of varying length.
It is the Power Query version of Excel’s LEFT. Text.Start([Column],n) does what LEFT(cell,n) does on a worksheet.
A negative count throws. You will get Expression.Error: The count argument should be greater than or equal to zero. Pass 0 or a positive number only.
It always counts from the left. For the last N characters use Text.End, for a substring from a given position use Text.Middle, and for a zero-based start index with an optional length use Text.Range. To test whether a value appears anywhere in the text instead, see Text.Contains.

Common questions about Text.Start

How do I do a LEFT() in Power Query?

Use Text.Start. Text.Start([Column],n) returns the first n characters, exactly like LEFT(cell,n) does in a worksheet.

What is the difference between Text.Start, Text.End, and Text.Middle?

Text.Start takes characters from the left (like LEFT), Text.End takes them from the right (like RIGHT), and Text.Middle pulls a substring beginning at a position you choose (like MID).

List of All Power Query Functions

Related Power Query Functions / Articles: