M language - extract 5 digit numbers only from string-CodePudding

I am trying to write a custom column in M to detect whether the field contains a 5 digit number, and then extract that 5 digit number into a new column. When employees incur an expense, they need to specify the job number if its for a job. If not, they usually type in text.

IF text.contains number like "#####" then have the new column have that 5 digit number, else null.

I am having incredible difficult on how to write in M. I tried doing this in

CodePudding user response：

Assuming your data is in column [Column1] then try

Add column.. custom column ... with formula:

= try if Number.From([Column1])>0 and Text.Length(Text.From([Column1]))=5 then [Column1] else null otherwise null

that looks for Column1 being something that can evaluate to a number, and that has a length of 5 characters when viewed as a string, otherwise puts a null. This allows for leading zeroes like '01234 but does not attempt to account for mixed text/numbers such as A12345

if you need to remove alphas, try

= if Text.Length(Text.Select(Text.From([Column1]),{"0".."9"}))=5 then Text.Select(Text.From([Column1]),{"0".."9"}) else null

CodePudding user response：

Given what you have written, that valid entries will be in the range of 10,000 to 99,999 and may or may not be preceded by a J, you can add a column to detect that.

//Ensure Column1 (or whatever it's real name is) is of type text and NOT any
   #"Added Custom" = Table.AddColumn(#"Previous Step", "Job Number", each 
        let 
            x = Text.TrimStart(Text.Upper([Column1]),"J"),
            n = try Number.From(x) otherwise 0
        in 
            if n>=10000 and n<100000 then n else null)