While working with data in Power Query, you may want to count strings over X characters, and the best way to do such is to create a custom column. This can be useful when you need to analyze data that includes text fields with a specific character length.
In this article, I will explain a simple process to create a custom column in Power Query that counts strings over a specific character.
Let;’s dive right in!
Open Power Query Editor
To start with, open the Power BI Desktop. In the “Home” tab, click on the “Transform Data” button. This will open the Power Query Editor, where you can transform and shape your data.
The Power Query Editor interface includes a set of buttons, tabs, and panes that allow you to work with your data and create custom transformations.
Select the Column to Count
After you have opened the Power Query Editor and imported your data, click on the header of the column you want to count to highlight the entire column.
Add a Custom Column
Once you select the column you want to count, next is to add a custom column to the data. To do that, go to the “Add Column” tab in the ribbon, and select the “Custom Column” button. This will open the “Custom Column” dialog box.
In the “Custom Column” dialog box, you will see a formula bar where you can enter the formula for your custom column.
This formula will be used to calculate the count of strings over certain characters in the selected column. Below is an example formula:
if Text.Length([Column Name]) > X then 1 else 0NB: Replace “Column Name” with the name of the column from which you want to count the strings. Then replace “X” with the minimum number of characters you want to count.
Finally, do not forget to rename the column, to easily identify the purpose of the column when working with your data. For example: Long String Count, String Length, etc.
Close and Apply the Changes
Once you enter the formula in the formula bar, click “OK” to apply the changes. Now, you will see a new column in your Power Query table that shows the string count for each row in your selected column.
Finally, go to the “Home” tab in the Power Query Editor to close and apply the changes.
FAQs
What is the formula language used in Power Query?
Power Query uses a formula language called M—a language that supports functions, data types, and variables.
Is Power Query a free tool?
Yes, Power Query is a free tool that is used in Microsoft Excel and Power BI Desktop.
However, there may be licensing or subscription requirements for using Power BI and some other Microsoft tools.
What are some common use cases for Power Query?
Power Query is very useful for data cleaning, transformation, and merging especially for big data, combining data from multiple sources, creating custom visuals, etc.
How to learn more about Power Query?
There are a lot of resources available for learning Power Query, such as online forums and tutorials, Youtube, etc.
Also, Microsoft offers a free online course called Analyzing and Visualizing Data with Power BI which includes a Power Query section.
Can Power Query automate data import and cleaning?
Yes, Power Query can automate data import and cleaning by creating custom queries and refreshing them regularly. This is mainly useful for large datasets that need to be updated frequently.
Conclusion: Creating a Custom Column in Power Query to Count Strings Over X Characters
Yes, that’s it!
As you can see, transforming data is a cakewalk with Power Query!
So, if you need to count strings with a certain character length, all you need to do is to follow the steps explained in this article! With this tool in your skillset, data analysis will be a cinch.
Thanks for reading!
If you enjoyed reading this, you may also like to read how to create a calculated index column in Power BI.