Text.Length

Updated on

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.

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”.

The Text.Length function is often more useful when applied on tables. Suppose you have a table filled with sentences and you want to get a quick overview of the length of each string. Simply apply the Text.Length function and you can immediately see the number of characters in each string.

Text.Length shows number of characters in Power Query M

Now, let’s get a little more advanced. Imagine a scenario where 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, we’ll need to combine the Text.Length function with another Power Query function: Text.Start. Let’s place the result in a column labeled “Extraction“. The formula would look something like this:

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 illustrate how you can use the Text.Length function to provide you with information. It’s effective for testing and manipulating text in Power Query M Language.

Learn more about Text.Length in the following articles:

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