Forum Discussion

KG1's avatar
KG1
Resolver I
5 years ago
Solved

Extracting Numbers from Text

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

 

DescriptionResultRequired Result
Road Closure 1234569 Any Lane HF1 9JA - Date 06/01/211234569190601211234569
Unplanned Road Closure 1234569 Nowhere Lane HF7 4JA1234569741234569
Emergency Road Closure 1234569 Somewhere Lane HF16 5JA12345691651234569
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

2 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.