In this article, we will explain how to use the Text.Length function in Power Query.
It can be used in combination with other functions to improve data quality and find the values you are looking for.
We will cover how to use Text.Length in a filter, how to use greater/less than in Text.Length formulas and how to resolve common errors with Text.Length.
How do you find the length of text in Power Query?
Text.Length is a text function in Power Query M code.
It is used to return the number of characters in a text field, i.e. the length of the text.
Here is some example data in Power Query of some different names:
To find the length of these text values in Power Query, we can create a custom column and use Text.Length.
The formula for the custom column would be:
= Text.Length([Name])Text.Length just requires you to specify the column you want. In this case, it is the ‘Name’ column.
Here is the result of the custom column:
It has returned the number of characters (length) of each name.
How to use Text.Length in a filter
Text.Length can also be used in a filter, not just a custom column.
This means you can filter a text column based on the number of characters. It also saves you from having to create a custom column for this purpose too.
Using the same example data from the section above, we would apply any filter (e.g. select ‘James’ as the only name).
This creates a Filtered Rows applied step that we can edit and input our Text.Length formula.
In our example, we have edited the red section of the M code below:
This will filter the column to only show names with 5 characters.
Here is the M code you can edit for your applied step:
= Table.SelectRows(#"Changed Type", each Text.Length([Name]) = 5)Here is the result of this filter:
Errors with Text.Length
Errors experienced while using Text.Length are usually due to the function being applied to columns that are not text data type.
Text.Length only works when being applied to text values, so you must either change the data type of your column or integrate the Text.From function in your step.
Here’s an example to show you how to fix common Text.Length errors.
Our example data (names and ages) is below:
If we create a custom column using Text.Length for the names, as below, it will return the number of characters in each cell and ‘0’ if it is blank.
= Text.Length([Name])Here is the result:
However, if we create a custom column using Text.Length for the ages, as below, it will return nulls or errors.
= Text.Length([Age])Here is the result:
If the Age is null, then the Text.Length will also return null. However, if there is an Age value, since it is type number, it will return an error.
We can solve this by nesting the Text.From function in our custom column.
This means Text.Length will now be able to look at the number of characters in the cell and it will return the correct values.
Here is the custom column formula we would use:
= Text.Length(Text.From([Age]))Here is the result:
Text.Length with greater than and less than
Greater than or Less than symbols can be used in Text.Length formulas to return certain values.
For example, if we use the data from our first section, we could use the following M code to find Names with a length of 4 to 6 characters:
= Table.SelectRows(#"Changed Type", each Text.Length([Name]) > 3 and Text.Length([Name]) < 7)This will return values where the number of characters is greater than 3 and less than 7.
Text.Length can also be used within an IF statement to return certain values when true or false.