Splitting Columns without generating new column

Try this

Open Power Query Editor:
In Power BI Desktop, go to the “Home” tab and click on “Transform data” to open the Power Query Editor.
Create a Custom Function:
Go to the “Home” tab and click on “Advanced Editor”.
Create a new custom function to extract the numeric part from the text. Here’s an example of how to define this function:
let
ExtractNumeric = (inputText as text) as text =>
let
// Remove the prefix "EF-"
textWithoutPrefix = Text.Replace(inputText, "EF-", ""),
// Extract the numeric part before the closing parenthesis
numericText = Text.BeforeDelimiter(textWithoutPrefix, ")")
in
Text.Trim(numericText)
in
ExtractNumeric

Apply the Custom Function to Multiple Columns:
Go back to the main query where your data is.
For each column you want to transform, add a custom column that uses the ExtractNumeric function.
Here’s how you can do it:
Go to the “Add Column” tab and click on “Custom Column”.
Name the new column (e.g., NumericPart_Column1).
Use the custom function in the formula box:
ExtractNumeric([Column1])

Repeat this step for each column you want to transform (e.g., Column2, Column3, etc.).
Remove Original Columns:
After creating the new columns with the numeric parts, you can remove the original columns if you no longer need them.
Right-click on each original column and select “Remove”.
Close and Apply:
Click on “Close & Apply” to apply the changes and return to the Power BI report view.
Example
If your columns have values like EF-3) Some random Text, the new columns will have just the numeric part:

Before:

Column1
EF-3) Some random Text

After:

NumericPart_Column1
3