Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

M Language - Retrieve String of Contiguous Numbers from a String

Hello everyone,

Is there a way  to retrieve a string of  contiguous numbers from a string ?

The only requirement is that the numeric characters are contiguous.

 

Let me illustrate:

1) ABCDE234 --> 234

2) ABC456-DD --> 456

3) 134ABC-CC --> 134

4) CC-543_FF --> 543

5) CC_FF-334567 --> 334567

Thanks in advance.

 

Jason.

Need Help
  • smpa01's avatar
    smpa01
    7 years ago

    Anonymous By the way there is also a R solution to this problem.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRydnE1MjZRitUBc0xMzXRdXMA8Q2MToICuszOY5+ysa2piHO/mBuUBWbrGxkDl5kqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Run R script" = R.Execute("# 'dataset' holds the input data for this script#(lf)x<-dataset#(lf)x#(lf)library(stringr)#(lf)numextract <- function(string){str_extract(string, ""\\d+\\.*\\d*"")}#(lf)x$result<-as.numeric(numextract(x$Column1))",[dataset=#"Changed Type"]),
    #"""x""" = #"Run R script"{[Name="x"]}[Value]
in
    #"""x"""

4 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    7 years ago

    Anonymoussure it can

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRydnE1MjZRitUBc0xMzXRdXMA8Q2MToICuszOY5+ysa2piHO/mBuUBWbrGxkDl5kqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"),
    #"Split Column by Position" = Table.SplitColumn(#"Duplicated Column", "Column1 - Copy", Splitter.SplitTextByRepeatedLengths(1), {"Column1 - Copy.1", "Column1 - Copy.2", "Column1 - Copy.3", "Column1 - Copy.4", "Column1 - Copy.5", "Column1 - Copy.6", "Column1 - Copy.7", "Column1 - Copy.8", "Column1 - Copy.9", "Column1 - Copy.10", "Column1 - Copy.11", "Column1 - Copy.12"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1 - Copy.1", type text}, {"Column1 - Copy.2", type text}, {"Column1 - Copy.3", type text}, {"Column1 - Copy.4", type text}, {"Column1 - Copy.5", type text}, {"Column1 - Copy.6", type text}, {"Column1 - Copy.7", type text}, {"Column1 - Copy.8", type text}, {"Column1 - Copy.9", type text}, {"Column1 - Copy.10", Int64.Type}, {"Column1 - Copy.11", Int64.Type}, {"Column1 - Copy.12", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each try Number.From([Value]) otherwise -999),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] <> -999)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Value"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Column1"}, {{"AD", each _, type table}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each let
    Source = [AD],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Custom"}),
    Custom = #"Removed Other Columns"[Custom]
in
    Custom),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"AD"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns2", {"Custom", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Extracted Values",{{"Custom", Int64.Type}})
    in
    #"Changed Type2"

  • smpa01's avatar
    smpa01
    Community Champion
    7 years ago

    Anonymous By the way there is also a R solution to this problem.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRydnE1MjZRitUBc0xMzXRdXMA8Q2MToICuszOY5+ysa2piHO/mBuUBWbrGxkDl5kqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Run R script" = R.Execute("# 'dataset' holds the input data for this script#(lf)x<-dataset#(lf)x#(lf)library(stringr)#(lf)numextract <- function(string){str_extract(string, ""\\d+\\.*\\d*"")}#(lf)x$result<-as.numeric(numextract(x$Column1))",[dataset=#"Changed Type"]),
    #"""x""" = #"Run R script"{[Name="x"]}[Value]
in
    #"""x"""

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      smpa01  Quite impresive.

      I like the part where you use

      try Number.From([Value]) otherwise -999

      However, you lost me when you started doing this:

      let
          Source = [AD],
          #"Removed Other Columns" = Table.SelectColumns(Source,{"Custom"}),
          Custom = #"Removed Other Columns"[Custom]
      in
          Custom

      Not sure what is going on here....

      • smpa01's avatar
        smpa01
        Community Champion
        7 years ago

        Anonymousit is doing this. I am only interested in Custom Column so that I can convert that into a list.

         

        I grouped the data by Column1 so that I can run the follwoing in each group cluster

         

        let
            Source = [AD],
            #"Removed Other Columns" = Table.SelectColumns(Source,{"Custom"}),
            Custom = #"Removed Other Columns"[Custom]
        in
            Custom