Do you wonder how to extract numbers from a string in Power BI? This article explains an easy approach to doing that.
Follow the step-by-step method and practice while doing that.
Let’s get started.
Getting started: How to Extract Numbers From a String in Power BI
To start this, open your Power BI Desktop, insert the data you want to work with, and navigate your way through the Power Query Editor.
Here is a view of my Power Query Editor, where I will extract the numbers from the strings (ab3675ghym, asg5687hyg90, etc.) in the worksheet.
To do this, select Custom Column
The Custom Column Window
The above process automatically takes you to the custom column window (where you will write some formula to extract numbers from the string.
Features of the custom column window
- A list of columns you can use on the right
- In the New column name box, type the name of the column you made. You can give this column any name of your choice.
- In the Custom column formula box, you can use formula queries. You make these queries by putting together the formula that defines your new custom column.
I renamed my New column name to strings (as you can see below).
Creating the Formulas
- From the Available columns on the right, click Insert or double-click to add them to the custom column formula (In this tutorial, I have just one column: Column1)
- Observe the indicator at the bottom of the Custom Column window as you enter the formula and build your column
- If there are no mistakes, a green checkmark and the message will appear. This means your formula has no syntax errors
Here is the formula again.
= Text.Remove([Column1],
Text.ToList(Text.Remove([Column1],{"0".."9"})))Power BI Desktop says the code has no syntax error. Now, press “OK“
Automatically, a new column will be created, just beside your “Column1” titled “Strings” containing the numbers extracted from your strings in Column1.
Conclusion: How to Extract Numbers From a String in Power BI
That is a simple approach to how you can extract numbers from a string in Power BI Desktop. Simple, right? Yeah, it is.
Thanks for reading.