M Language - Retrieve String of Contiguous Numbers from a String

[Anonymous]

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.

[smpa01 · Accepted solution]

@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]

@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]

@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

[smpa01]

@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"