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

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