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

If you want to count the number of characters in a text value in Power Query, the Text.Length function is what you reach for. It returns the character count as a number.

It is available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of Text.Length Function

Text.Length(text as nullable text) as nullable number

where

text (required, nullable text). The text value whose characters you want to count.

Returns: a number, the count of characters in text. If text is null, it returns null.

In plain terms, it is the Power Query M equivalent of Excel’s LEN function.

Example 1: Count the characters in a text value

Count how many characters are in the word Spreadsheet.

let
    Source = Text.Length("Spreadsheet")
in
    Source

Result: 11

The word has 11 letters, so the function returns 11.

Example 2: Add a length column to a table

The most common use is measuring the text in each row of a column with Table.AddColumn.

Say you have a ProductSKUs query with a single SKU column.

Here is the starting data:

SKU
KB-100
MOUSE-22
HDMI-7
USB

Now add a column that holds the length of each SKU:

let
    Source = Excel.CurrentWorkbook(){[Name="ProductSKUs"]}[Content],
    Added = Table.AddColumn(Source, "Length", each Text.Length([SKU]), Int64.Type)
in
    Added

The result produces:

SKU	Length
KB-100	6
MOUSE-22	8
HDMI-7	6
USB	3

Each row now shows its own character count, typed as a whole number with Int64.Type.

Example 3: Keep rows by text length

You can also use Text.Length as a row condition inside Table.SelectRows.

Say you have a Usernames query and you want to keep only the usernames longer than six characters.

Here is the starting data:

Username
jdoe
amandar
tk
priyasingh
max99

Now filter the rows where the username length is greater than 6:

let
    Source = Excel.CurrentWorkbook(){[Name="Usernames"]}[Content],
    Filtered = Table.SelectRows(Source, each Text.Length([Username]) > 6)
in
    Filtered

The result produces:

Username
amandar
priyasingh

Only amandar (7 characters) and priyasingh (10 characters) pass the test. The rest are dropped. To filter on what the text says rather than how long it is, use Text.Contains instead.

Example 4: Spaces and punctuation count too

Text.Length counts every character, not just letters and digits.

Say you have an Entries query with a mix of spaces and punctuation in the Entry column.

Here is the starting data:

Entry
AB 12
X-Y-Z
a b c
Hi!

Now add a length column the same way as before:

let
    Source = Excel.CurrentWorkbook(){[Name="Entries"]}[Content],
    Added = Table.AddColumn(Source, "Length", each Text.Length([Entry]), Int64.Type)
in
    Added

The result produces:

Entry	Length
AB 12	5
X-Y-Z	5
a b c	5
Hi!	3

AB 12, X-Y-Z, and a b c all return 5 because the spaces and hyphens each add 1. Hi! returns 3 because the exclamation mark counts as a character.

Things to keep in mind with Text.Length

null returns null, not 0. If a column can be blank, guard it with Text.Length([Column] ?? "") so empty values count as 0 instead of producing null.
Leading and trailing spaces count. If you want the length without surrounding whitespace, clean the value first with Text.Trim, then measure it.
The input must be text. A number or date throws an error first. Convert it with Text.From, for example Text.Length(Text.From([Quantity])).

Performance and query folding

Text.Length is a lightweight local operation. On Excel, CSV, or folder sources it runs in memory, which is fine for thousands of rows.

List of All Power Query Functions

Related Power Query Functions / Articles: