Replicating Excel’s LEN Function with M in Power Query

When working with data, one of the key tasks is to know the length of text entries. Power Query provides a simple way to count the length of a text. In this article, I will show you how to replicate LEN function with M in Power Query.

Key Takeaways:

• LEN function counts the number of characters in a string.
• In Power Query, Text.Length() is the equivalent of the LEN function.
• Text.Length function counts letters, numbers, spaces, and punctuation.
• Go to Add Column > Custom Column to create a text length calculation.
• Character counts can help with data validation, cleaning, and analysis.

 

LEN Function in Excel

What is LEN and How Does it work?

The LEN function in Excel is used to return the number of characters in a text string. The syntax of this function is:

=LEN(text)

Excel counts and displays the total number of characters, including spaces, punctuation, and numbers.

Why Use LEN in Data Analysis?

The LEN function is a key tool in data analysis for several reasons.

• It allows us to verify the consistency and accuracy of data entries by checking their length.
• It can be used to identify and resolve data quality issues.
• It can also be used to clean, validate, and format data.

 

Creating a Custom LEN Function in Power Query

STEP 1: Select your data and go to Insert > Table.

STEP 2: Go to Data > Get & Transform > From Table

Excel 2016:

 

Excel 2013 & 2010:

Go to Power Query > Excel Data > From Table (Excel 2013 & 2010)

STEP 3: This will open up the Power Query Editor.

We want to get the length of the Channel Partners, so we need to select the CHANNEL PARTNERS column.

Go to Add Column > Add Custom Column

STEP 4: Let us create a simple M expression to replicate the LEN function in Excel.

• In the New column name text box, type CHANNEL PARTNERS (LEN)
• In the Custom column formula, type in: Text.Length(
• From the Available columns choose CHANNEL PARTNERS and select Insert.
• Then finish off the formula by entering  ) 

We now have build the following formula: Text.Length([CHANNEL PARTNERS])

So lets quickly break down what we just did:

• We are using the Text.Length formula to get the length of the CHANNEL PARTNERS column
• Click OK to confirm.

Now you will see your changes take place.

STEP 5: Click Close & Load

Congratulations! You have used a M formula for replicating the LEN function!

 

Practical Uses of Text.Length in Power Query

Text.Length is useful for validating and cleaning data. For example, you can check whether customer IDs, postal codes, or product codes meet a required character length. It can also help identify unusually short or long text entries that may contain errors.

You can use these character counts to filter records, improve data quality, and prepare data for systems that have character limits

 

FAQs

1. What is the Power Query equivalent of the LEN function?

The Power Query equivalent of LEN is Text.Length function.

2. Does Text.Length count spaces?

Yes, it counts all characters, including spaces and punctuation.

3. How to use Text.Length in Power Query?

• Open your data in Power Query Editor.
• Go to Add Column > Custom Column.
• Enter the formula: Text.Length([Column Name]).
• Click OK to create the new column.

4. Can I count the length of numbers with Text.Length?

Yes, but you may need to convert the numbers to text first.

5. Why would I use Text.Length in Power Query?

It helps validate, clean, and analyze text data based on character count.