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 numberDescription
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 11This 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.
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.
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.
Related articles
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
