How do I create a column that extracts the last numeric value from a string

Question

How do I create a column that extracts the last numeric value from a string?
I need to create a new column in Power BI that extracts the last numeric value from a text string. The strings may contain a mix of letters, numbers, and special characters, and only the final numeric portion is needed. What is the most efficient method using Power Query M functions or DAX to isolate and extract this last numeric value accurately?

score 0 · Answer 1 · 4 days

In Power BI, Power Query (M language) is the best approach to extract the last numeric value from a text string, besides being flexible. This is the best procedure if the string is complex.

Power Query Solution (preferred)
He will need to follow the steps in the Power Query Editor:

Transform Data. Select the Column by clicking Add Column> Custom Column while selected.

let
    text = [YourColumnName],
    numbers = List.Select(Text.ToList(text), each Value.Is(Value.FromText(_), type number)),
    reversedText = Text.Reverse(text),
    matches = Text.RegexReplace(reversedText, "[^\d]+", "|"),
    lastNumberReversed = List.Last(Text.Split(matches, "|")),
    lastNumber = Text.Reverse(lastNumberReversed)
in
    lastNumber

Replace [YourColumnName] with the actual name of your column.

Explanation

Text.Reverse(text) ensures we start from the end.
Text.RegexReplace(..., "[^\d]+", "|") replaces all non-digit sequences with a delimiter.
List.Last(...) gets the first numeric group from the end.
Text.Reverse(...) flips it back to the correct order.