Extracting Numbers from Text

[KG1]

Hi

I need to be able to extract the order reference number from a text column

The description contains the order reference number I need

I have had a look at similar threads and found this solution 

= Text.Select([String],{"0".."9"})

Because there are more numbers in the description field this doesnt give me the result I need

Any help would be greatly appreciated - thank you in advance

Description	Result	Required Result
Road Closure 1234569 Any Lane HF1 9JA - Date 06/01/21	123456919060121	1234569
Unplanned Road Closure 1234569 Nowhere Lane HF7 4JA	123456974	1234569
Emergency Road Closure 1234569 Somewhere Lane HF16 5JA	1234569165	1234569

[Anonymous · Accepted solution]

Hi @KG1 ,

The whole M code is shown below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcw7C4MwGIXhv3LI3KJJNMEx9EKR0qGlkziE+nkBTYqtiP++Ii4Fx/fAc7KM3b0tcGj9Z+gJXMgoVgmMm3C1jnA5cySpwR5H+yWEKgh5IDjLdxl7undrnaMCmx83P9Y09/qjEaVmcaeO+orca9p2D9/Rn+QK8UpLglAx6qEhSL1MWqGq5VwRmqEZWZ7/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Description", "Description - Copy"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Duplicated Column", "Description - Copy", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Description - Copy.1", "Description - Copy.2", "Description - Copy.3", "Description - Copy.4", "Description - Copy.5", "Description - Copy.6", "Description - Copy.7"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Character Transition",{"Description", "Description - Copy.1", "Description - Copy.2"}),
    #"Split Column by Character Transition1" = Table.SplitColumn(#"Removed Other Columns", "Description - Copy.2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Description - Copy.2.1", "Description - Copy.2.2"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Split Column by Character Transition1",{"Description", "Description - Copy.2.1"})
in
    #"Removed Other Columns1"

Actually, if the first string is always end with Closure, you could simply use this M syntax:

= Table.AddColumn(#"Changed Type", "Text Between Delimiters", each Text.BetweenDelimiters([Description], "Closure ", " "), type text)

Or  the length of order reference number is fixed——always 7 ,then you could use LEFT() to extract the Result like this:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @KG1 ,

you can use this M snippet, this M snippet creates a new column called "Custom".
Paste this snippet in your query using the Advanced Editor (if you never used the Advanced Editor before, make a copy of your pbix).
Close the last line above the final in with a comma.

Paste the code snippet above the last in in your query. 

As the step is called #"Added Custom", don't forget to use this name after the final in.

// the result column that contains the 1st number from the column description
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", 
        // passes the current row to an inline function
        (row) => 
        let 
            // splits the column description into a list, the items are separated by a space
            DescToList = Text.Split( row[Description] , " " ),
            // counts the items o the list
            NoOfItems = List.Count( DescToList ) - 1,
            // extracts the first number of the list of numbers
            TheFirstNumber =
                // removes the nulls from the inner list
                List.RemoveNulls(
                    // the inner, an interation across the items  
                    List.Generate(
                        () => [x  = 0 , y = try Number.From( DescToList{ 0 } ) otherwise null ]
                        , each [x] < NoOfItems
                        , each [x = [x] + 1 , y = try Number.From( DescToList{ [x] + 1 } ) otherwise null ]
                        , each [y]
                    )
                ){0}
            in TheFirstNumber
    )
in
    #"Added Custom"

Here is a screenshot of the result based on the sample data you provided:

Hopefully, this is what you are looking for.

Regards,

Tom