Text.Length - Text Function

Text.Length is a Power Query M function that calculates the number of characters in a text value. The function returns the character count of the input text.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Description

The Text.Length function counts the number of characters present in any given text.

Examples

Now that we have a grasp of what the Text.Length function does, let’s dive into some practical examples of how this function operates.

Simple Example: Counting Characters

Consider a straightforward example using the well-known phrase “Hello World”. If we want to know the number of characters in this phrase using Power Query’s Text.Length function, we would set it up as follows:

Text.Length( "Hello World" ) // Returns 11

This returns a value of 11, indicating there are 11 characters in the string “Hello World”.

Applying Text.Length to Columns

The Text.Length function is particularly useful when applied to columns in a table. Suppose you have a table filled with sentences and you want to quickly see the length of each string. Simply apply the Text.Length function to get the number of characters in each string.

Text.Length shows number of characters in Power Query M

Advanced Example: Limiting Character Length

Imagine you want to limit the number of characters to a maximum of 18. For entries that exceed this limit, you want to truncate them at the 18th character and append “…” at the end. To achieve this, combine the Text.Length function with Text.Start. Here’s how you can do it:

if Text.Length( [Sentence] ) > 18 
   then Text.Start( [Sentence], 18 ) & "..." 
   else [Sentence]

This function checks if the length of the sentence is greater than 18 characters. If it is, it extracts the first 18 characters, and appends “…” to the end. If not, it simply retains the original sentence.

Text.Length to conditionally extract characters in Power Query M

These examples show how you can use the Text.Length function to provide you with information.

Learn more about Text.Length in the following articles:

Text Functions in Power Query M (150+ Examples)
Your guide to Text Functions in Power Query M. Learn from practical examples and master Power Query’s most useful Text functions. » Read more

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/text-length