Replicating Excel’s LEN Function with M in Power Query

When working with data, one of the essential tasks often involves determining the length of text entries in datasets. If you’re transitioning from Excel to Power Query, you might be wondering how to replicate the functionality of Excel’s LEN function in this new environment. Power Query, known for its advanced data transformation capabilities, allows for custom function creation to mimic such Excel functions. I’ll guide you through the process, ensuring you’re equipped to handle text length calculations seamlessly.

Key Takeaways:

• Excel’s LEN function counts the number of characters in a string, including spaces and punctuation.
• Power Query replicates LEN using the Text.Length() function within custom columns.
• You can calculate text length by adding a Custom Column using a simple M formula.
• Best practices include naming conventions, error handling, and testing for accuracy.
• Use Power Query’s LEN equivalent for automating bulk data cleaning and validation.

 

Unpacking the LEN Function in Excel

What is LEN and How Does it Work?

The LEN function in Excel is a straightforward yet powerful tool designed to return the number of characters in a text string. By simply inputting =LEN(text), Excel counts and displays the total number of characters, including spaces, punctuation, and numbers.

This function is incredibly useful for a range of data analysis tasks, such as validating data input, ensuring consistent text formatting, and managing datasets involving text data. It helps provide quick insights into the structure and length of text fields, aiding in more efficient data handling and processing. By understanding and utilizing the LEN function, we unlock greater potential for precise data analysis.

Why Use LEN in Data Analysis?

The LEN function is an invaluable tool in data analysis for several reasons. It allows us to quickly verify the consistency and accuracy of data entries by assessing their length. For instance, it ensures that data fields meet required specifications, such as character limits in usernames or product codes. Additionally, LEN can aid in identifying and resolving data quality issues. Detecting unusually long or short entries can signal data entry errors or the presence of extraneous spaces. By leveraging LEN, we enhance our ability to clean, validate, and format our data, ensuring that our datasets are both precise and reliable for subsequent analyses.

 

Creating a Custom LEN Function in Power Query

Let’s go through the steps in detail:

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

 

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 from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated values.

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

 

Best Practices for Function Implementation

Implementing a custom function in Power Query can enhance your data processing capabilities, but ensuring its effectiveness requires adherence to best practices:

• Consistency in Naming: Define clear and descriptive names for your functions. This helps avoid confusion, especially when you work with multiple custom functions. Use names that are easy for others (or your future self) to understand at a glance.
• Error Handling: Incorporate robust error handling within your function. Use conditional statements or try-catch constructs to manage unexpected inputs or potential errors gracefully, ensuring that your function can handle a variety of data scenarios without breaking.
• Documentation and Comments: Within the Advanced Editor, provide comments (using double forward slashes, //) to explain key parts of your function. This documentation is useful for both you and others who may work with your queries, helping clarify the function’s purpose and logic.
• Optimization: Avoid unnecessary computations by optimizing your function’s code. This can mean reducing the number of transformations or only calling the custom function when needed, which improves performance, especially with larger datasets.
• Testing: Before full implementation, test your function with a diverse set of data samples. Include edge cases to ensure accuracy and reliability. Validate outcomes against known benchmarksto confirm its consistent performance across different scenarios.

By adhering to these best practices, you enhance the reliability and efficiency of your custom functions in Power Query, fostering a more productive data preparation process.

 

FAQs

What are the differences between LEN in Excel and Power Query?

Excel’s LEN function operates interactively within a cell-based framework, providing immediate character counts for text strings. In contrast, Power Query’s equivalent, such as Text.Length, processes text data during data transformation stages, allowing for batch processing and automation. Power Query’s approach is more suited for large-scale, repetitive operations, while Excel offers more direct and immediate results on a smaller scale.

How do I use the LEN function in Power Query?

In Power Query, you replicate Excel’s LEN function using Text.Length([ColumnName]). This M formula is added via the “Add Custom Column” feature. It returns the character count of each row’s text value. This is ideal for processing large datasets and ensuring consistency in text fields.

Why switch from Excel LEN to Power Query Text.Length()?

While Excel’s LEN is useful for quick analysis, Power Query’s Text.Length() is better suited for transforming large or complex datasets. It integrates seamlessly into automated workflows and doesn’t require manual intervention. Power Query also enables batch processing, saving time and reducing human error. It’s more powerful when dealing with repetitive or structured data transformations.

Can Power Query LEN function handle errors in text fields?

Yes, with proper error handling in your M code, Power Query can gracefully manage nulls or unexpected data types. You can use conditional statements like if Text.HasValue([Column]) then Text.Length([Column]) else 0. This prevents query failure and ensures robust performance. Always test your custom column with different input types for reliability.

Does Text.Length count spaces and symbols too?

Absolutely. Just like Excel’s LEN function, Text.Length() includes spaces, punctuation, and special characters in its count. For example, “Hello, World!” would return 13 characters. This behavior is useful when validating formats like codes, emails, or inputs with length constraints.