Downloads
Power Query MTextBeginner

Text.Length

Counts the characters in a text value.

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

Returns: The number of characters, or null when the input is null.

What it does

Text.Length counts characters. In finance it is mostly a data quality check. An account code should be exactly six characters, a bank routing number nine, and a memo field should not exceed a set limit that the target system enforces. Counting the characters finds the rows that break the rule before they reach a report.

Syntax and arguments

Example

An ERP export is supposed to carry six digit account numbers. Rows with any other length are flagged so the team can fix them in the source system.

let
    Accounts = #table(type table [Account = text, Name = text],
        {{"100000", "Cash"}, {"21000", "Accounts payable"}, {"4010", "Product sales"}}),
    Checked = Table.AddColumn(Accounts, "Length", each Text.Length([Account]), Int64.Type),
    Flagged = Table.SelectRows(Checked, each [Length] <> 6)
in
    Flagged

Two rows have the wrong length, "21000" and "4010". The Cash row has six characters and passes. The team can see which codes need attention without reading all of them.

Example: a second, different use

Count the characters in a vendor name to find names too long for a check printer's payee line, so they can be shortened before the run.

Things to watch

In business terms

Length checks are the cheapest control in a data load. They catch truncated account numbers, malformed bank references and oversized memos before a bad row reaches the close.

Related functions

All Power Query M functions · View as Markdown · M basics