[solved] Keep only numbers in a string / remove all non-numbers
In the realm of data analytics and processing, Power BI has established itself as a formidable tool for transforming raw data into insightful, actionable intelligence. One common task analysts face is the need to sanitize and format data appropriately for analysis. Specifically, extracting only numeric values from strings is a frequent requirement, especially when dealing with mixed data types. This blog explores how to keep only numbers in a string in Power BI, including handling special characters like dots and minus signs.
Why Keeping Only Numbers Matters
Data comes in various formats, and not all of it is immediately usable for analysis. When dealing with strings that contain a mix of numbers, letters, and special characters, extracting the numeric part can be crucial for several reasons:
- Accuracy in Calculations: Numeric operations require numeric data. Extracting numbers ensures that calculations are carried out correctly.
- Data Consistency: Ensuring that data adheres to a consistent format is key to reliable analysis.
- Efficiency: Cleaning data at an early stage saves time and resources downstream.
Knowing how to extract numbers from strings in Power BI is a valuable skill that enhances data preparation efforts.
How to Keep Only Numbers in a String in Power BI
Power BI provides several ways to transform data, one of which is through the use of custom column formulas. This section will guide you through the process of keeping only numbers in a string using Power BI’s formula language, M.
Basic Extraction
To extract only the numbers from a string column, you can use the Text.Select
function. Here’s a simple formula to add a new column that contains only the numeric part of your string data:
= Text.Select( [Column], {"0".."9"} )
In this formula, [Column]
represents the name of your string column that contains mixed characters. By specifying {"0".."9"}
, you instruct Power BI to select only characters in the range from 0 to 9, effectively filtering out all non-numeric characters.
Handling Dots and Minus Characters
In certain cases, you might need to retain dots (.) and minus (-) characters in your strings, especially when dealing with decimal numbers and negative values. To accommodate these characters, you can amend the formula as follows:
= Text.Select( [Column1], {"0".."9", "-", "."} )
Adding "-"
and "."
to the selection criteria ensures that these characters are preserved in the output.
Transforming the Existing Column
If you prefer to transform the existing column instead of adding a new one, you can use the Table.TransformColumns
function. This approach modifies the original column in place:
= Table.TransformColumns( #"PreviousStepName", {{"Column", each Text.Select( _ , {"0".."9","-","."} )}} )
Here, #"PreviousStepName"
refers to the name of the previous step in your query. This formula applies the Text.Select
function directly to each value in the specified column, resulting in a transformed column that contains only the desired characters.
Get a Free AI Website Audit
Automatically identify UX and content issues affecting your conversion rates with Flowpoint's comprehensive AI-driven website audit.
Real-World Application
Imagine you’re an analyst working with web analytics data that contains a column representing user session durations mixed with letters indicating units (e.g., '120s' for 120 seconds). By applying the techniques described above, you can extract the numeric part ('120') for consistent and accurate analysis of session durations.
Conclusion
Proper data preparation is a cornerstone of effective data analysis and visualization, and knowing how to extract only numbers from a string is an essential skill for Power BI users. By employing custom column formulas, you can ensure that your data is clean, consistent, and analysis-ready.
Utilizing a platform like Flowpoint.ai, you can further enhance your data analytics efforts. Flowpoint.ai helps you identify all the technical errors impacting conversion rates on a website and directly generates recommendations to fix them, thereby complementing your data transformation tasks in Power BI.
By mastering these techniques, you’ll be better equipped to tackle various data cleaning challenges, enabling you to focus on deriving meaningful insights from your data.