Power Query String Length: How to Count Characters

In Power Query, use Text.Length([ColumnName]) to return the number of characters in a text value. For example, add a custom column with Text.Length([Comment]); it counts spaces too, returns 0 for an empty string, and returns null when the input is null.

What Text.Length Counts

Text.Length is Power Query M’s function for measuring text length. It returns a number for the characters in a text value, not a word count, byte count, or estimate of how much space the text will occupy on screen. A string such as Power Query has a length of 11 because the letters and the space are all included.

This distinction matters when you are checking imported data. A value that looks blank may contain one or more spaces, and those spaces contribute to its length. An empty text value, written in M as "", has length 0. A null value means that no value is present; it is different from an empty string, and Text.Length(null) returns null rather than 0.

The function reports character length, not visible width. A narrow letter and a wide letter each count as one character, even though they may take different amounts of space in a font. Unicode text can also include symbols and combined characters whose internal representation does not map neatly to what a person perceives as one displayed symbol. If a system requires an exact limit for a particular kind of Unicode text, test representative values against that system’s rules rather than treating visual appearance as a length measurement.

Add a Length Column in Power Query

In Excel for Microsoft 365 on Windows, select a cell in your source table and choose Data > From Table/Range to open the Power Query Editor. Then add a calculated column using Add Column > Custom Column. Give the new column a name such as Character Count and enter this M expression, replacing Comment with the exact name of your text column:

Text.Length([Comment])

Select OK to create the column. Power Query evaluates the expression for each row, so each record receives its own count. When you are ready to return the results to Excel, choose Home > Close & Load. The resulting count is part of the query output and is recalculated when the query refreshes.

If you edit a query in the Power Query Editor on Excel for Mac, the same M expression works. The location and appearance of ribbon commands can differ between Mac and Windows builds, but the custom-column expression is unchanged. Excel for the web has fewer Power Query authoring capabilities than desktop Excel, so use the desktop editor if the needed query-editing command is unavailable in your browser.

Use the Function in Other Query Steps

A custom column is often the clearest way to inspect lengths, but the same function can appear anywhere a Power Query M expression is accepted. For example, you can add a column that labels codes of up to 12 characters as acceptable:

if Text.Length([Code]) <= 12 then "OK" else "Too long"

The comparison is inclusive: a value with exactly 12 characters receives OK, while a value with 13 characters receives Too long. This can help identify likely violations of a field-length rule before loading data into another system. Confirm the receiving system’s own definition of length, especially when it imposes limits based on bytes, encoded storage, or Unicode rules rather than ordinary character counts.

You can also filter on a length condition, or use a count column to sort rows and investigate unusually long entries. A useful workflow is to keep the original text alongside the calculated length. That way, a reviewer can see which value was measured instead of relying on a number without its source.

Handle Nulls, Empty Values, and Non-Text Data

Power Query distinguishes among null, an empty string, and text containing spaces. Those values may look similar in a preview, but they can produce different results:

Null: no value is present; Text.Length(null) returns null.
Empty text: "" is a text value with length 0.
One space: " " has length 1.
Text with leading or trailing spaces: those spaces are included in the count.

Text.Length expects text. If the source column contains numbers, dates, or mixed types, a row may produce an error when the function is applied directly. Convert values deliberately when a text representation is what you intend to measure. For example, Text.Length(Text.From([OrderNumber])) converts an order number to text before counting it. Conversion can depend on culture for values such as dates and decimals, so specify a culture when the exact formatted representation matters. Do not convert identifiers blindly: a numeric type may already have lost leading zeros, and converting it back to text cannot restore them.

If nulls should be treated as zero-length values for a particular analysis, make that choice explicit rather than assuming null already means zero. For instance, if [Comment] = null then 0 else Text.Length([Comment]) returns a number for both null and non-null rows. This changes the meaning of the result, so preserve nulls instead when they represent missing or unknown data.

Count After Cleaning or Trimming

Whether you should clean a value before counting depends on what the count is meant to describe. Counting the original value is appropriate when you need to audit exactly what was imported. If you want to measure the text after removing unwanted characters, apply the cleanup first and then count the result.

For example, Text.Length(Text.Trim([Comment])) counts the text after trimming whitespace at the beginning and end. It does not remove spaces between words. A value such as " hello " has length 9 before trimming and length 5 afterward. The original data is not changed by this expression; it produces a calculated result based on the trimmed version.

Text.Clean can remove control characters, which sometimes appear in data copied from external systems. A combined expression is Text.Length(Text.Clean(Text.Trim([Comment]))). That sequence trims the outside first, removes control characters next, and measures the result. For unusual whitespace, including nonbreaking spaces, inspect the source carefully: an ordinary trim or clean operation may not address every character you consider a space. Replace a known unwanted character explicitly when necessary, and retain the unmodified source if you need an audit trail.

Power Query Text.Length Compared With Excel LEN

Excel worksheet formulas and Power Query use different languages. In a worksheet cell, Excel’s function is LEN; in a Power Query custom column, the M function is Text.Length. They serve a similar purpose for ordinary text, but they are not interchangeable expressions. Entering =LEN(A2) in a Power Query custom-column box will not work as an M expression, and Text.Length([Comment]) is not a worksheet formula.

Choose where to calculate based on the data workflow. A Power Query length column is useful when the count should be recreated every time imported data refreshes, or when it is part of a repeatable cleaning and validation process. Excel’s LEN is useful for an ad hoc calculation on cells already loaded into a worksheet. If you use both, check a few representative values—particularly text containing unusual Unicode symbols—before assuming every edge case is handled identically.

Power Query’s count is also separate from a word count. A sentence with 20 characters is not necessarily a sentence with 20 words. To count words, you need a different process, such as splitting text on spaces and counting the resulting items, with extra decisions about punctuation, repeated spaces, and blank entries.

Common Problems and Practical Checks

If the new column shows errors, first check the column’s data type and inspect the error details. A field that appears to contain text may actually be typed as a number, or it may have mixed values. Convert values to text only when that is the intended measurement, and decide how nulls should behave. Also check that the column name in brackets matches the actual header. Names containing spaces or punctuation can still be referenced in brackets, for example [Customer Note].

If results are larger than expected, look for leading or trailing spaces, line breaks, or other nonprinting characters. If results are smaller than expected, check whether an earlier step already trimmed or transformed the source. Power Query applies steps in order: a length column added after a replacement or trim measures the transformed value, not necessarily the raw imported value. In the Applied Steps pane, select earlier steps to compare what the data looked like at different stages.

For a field with a maximum permitted length, do not rely only on an average or a few sample rows. Add a length column, sort it from largest to smallest, and inspect the longest values. You can also filter for values above the stated limit. Before rejecting records, verify whether the limit counts spaces and punctuation and whether it applies before or after normalization. Those details can differ across databases, APIs, and business rules.

Frequently Asked Questions

What is the Power Query formula for string length?
Use Text.Length([ColumnName]) in an M expression, replacing ColumnName with the column you want to measure. In a custom column, Power Query applies it to each row.

Does Power Query Text.Length count spaces?
Yes. Ordinary spaces inside the text and at its beginning or end count as characters. Trim the value before measuring if your requirement is to exclude leading and trailing whitespace.

What does Text.Length return for null or blank text?
Null input returns null, while an empty text value returns 0. Text containing a space is not empty and has a positive length.

How do I count a number as text in Power Query?
Convert it first with an expression such as Text.Length(Text.From([OrderNumber])). Be careful with identifiers: if a number column discarded leading zeros, converting it to text will not bring those zeros back.

Can I use LEN in Power Query?
No. LEN is the Excel worksheet function. In Power Query M, use Text.Length; the two expressions belong to different formula languages.

How do I count characters after removing extra spaces?
Use Text.Length(Text.Trim([ColumnName])) to count after trimming whitespace at the two ends. It keeps spaces between words, so use a separate replacement step if internal repeated spaces also need to be changed.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.