Extract Length Using Power Query or Get & Transform

Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.  One of the steps it allows you to take is to extract length of text. Doing this manually is tedious, especially with large datasets. That’s when I realized Power Query, or Get & Transform, could do the job effortlessly. In this article, I’ll walk you through how I extract length of text entries and filter them using Power Query.

Key Takeaways:

• Power Query allows me to transform and filter data without manual formulas.
• Extracting text length helps in identifying entries that meet specific criteria.
• Using tables is essential to make Power Query work efficiently.
• Number filters in Power Query enable dynamic, precise data selection.
• The process is repeatable and can be applied to different datasets with ease.

Power Query for Text Length Analysis

Why Use Power Query

I use Power Query because it allows me to clean, transform, and analyze data quickly without relying on complex formulas. Extracting text length is especially helpful when I want to filter entries based on the number of characters, like finding long names, product IDs, or descriptions that meet specific criteria. This saves me from manually scanning large datasets and ensures consistent results every time. By automating this process, I can focus on analyzing the results rather than spending time preparing the data. It’s a small step that adds significant efficiency to my workflow in Excel.

Benefits of Extracting Text Length

• I can quickly filter long or short entries, which is useful for reports or audits.
• It helps me identify anomalies or unusually long text strings that may cause formatting issues.
• I can use the filtered results for further analysis, like sorting, counting, or summarizing.
• The process is repeatable and can be applied across multiple datasets.
• It makes managing large datasets far more manageable and reduces manual errors.

 

Steps to Extract Length Using Power Query

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.

Make sure the Name column is selected. Go to Add Column > From Text> Extract > Length

Now we can get the names that are at least 12 characters long! Click on the Arrow beside the Length Column.

Go to Number Filters > Greater Than Or Equal To

Type in 12. Click OK.

STEP 4: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the filtered records!

You now have your new table with names at least 12 characters long!

 

Using and Maintaining Your Queries

Practical Applications

I use this technique to quickly identify long product names, employee IDs, or customer names in datasets. It helps me spot entries that might need attention or follow-up. I also use it to find unusually long strings in text data, such as descriptions or notes, which might affect reporting or formatting. This approach can be applied to marketing lists, HR data, financial records, and more. Essentially, it’s a universal method for filtering text by length in any Excel project.

Troubleshooting Errors

Sometimes the query doesn’t work as expected if there are blank cells or merged rows in the table. I always ensure the data is cleaned and formatted correctly before starting. If the Length column doesn’t appear, I check that the correct column is selected. Occasionally, Excel may prompt for data type changes — confirming the column as Text resolves most issues. Saving the query ensures I can reuse it on other datasets without repeating the setup steps.

 

FAQs

What is Power Query and why should I use it for text length analysis?

Power Query is a tool in Excel that lets me clean, transform, and analyze data without relying on complex formulas. I use it to automate repetitive tasks, like extracting text length from large datasets. This saves me from manually counting characters or filtering entries. It also ensures accuracy and consistency across my data. By using Power Query, I can focus on analyzing results rather than spending time on preparation.

How do I extract length of text in Excel using Power Query?

First, I turn my dataset into an Excel Table using Ctrl + T or Insert > Table. Then, I go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013/2010). In the Power Query Editor, I select the column I want and go to Add Column > From Text > Extract > Length. This automatically creates a new column with the character count for each entry. I can then filter or sort based on these lengths as needed.

How can I filter entries based on a minimum number of characters?

After extracting the length column in Power Query, I click the arrow beside the Length column. I go to Number Filters > Greater Than or Equal To and type the minimum number, like 12. Clicking OK instantly filters the dataset to show only entries that meet the criteria. This makes it easy to focus on names, IDs, or descriptions of a specific length. I can then close and load the filtered table back into Excel for further use.

What are some practical uses of extracting text length in Excel?

I use this method to identify long product names, employee IDs, or customer names quickly. It helps me spot unusually long entries that may cause formatting issues or need attention. I also apply it to marketing lists, financial records, or HR datasets where length criteria matter. This approach makes large datasets more manageable and highlights key information automatically. Essentially, it’s useful wherever text length impacts reporting or analysis.

What should I do if the Power Query results don’t appear correctly?

Sometimes the Length column doesn’t appear if the wrong column is selected or if there are blank or merged cells. I always check that the data is clean and the column type is set to Text. If Excel prompts for data type changes, I confirm it as Text to resolve most issues. Saving the query allows me to reuse it on other datasets without starting over. Cleaning the data and confirming column selection prevent errors and ensure accurate results every time.