Need regex type expression in M code

[Anonymous]

Hi,

I have a column that contains string values like:

Input	Output	Comments
IAM 2205	2205
SAP NO 2022 IY9	2022	IY9 is not purely a numerical value so it shoudnt be returned

I just want to extract the numbers from these strings that are given in the output as above. How can i do it?

[PhilipTreacy]

Hi @Anonymous 

Did this work for you?

Regards

Phil

[PhilipTreacy · Accepted solution]

Hi @Anonymous @Anonymous 

OK then try this.  Should work to extract any number of any length from a string as long as that number is delimited by spaces.

Download the PBIX file here

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nT0VTAyMjBVitWJVgp2DFDw81cwMjAyUvCMtISLRUSAxcB8sGRUlAJQXCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Sum(List.Transform(Text.Split([Input], " "), each try Number.From(_) otherwise 0)))
in
    #"Added Custom"

Regards

Phil

[PhilipTreacy]

Hi @Anonymous  @Anonymous 

So you just want to extract a 4 digit number from within a string? What about bits like IY9 as comments?

Regards

Phil

[PhilipTreacy]

Hi @Anonymous 

Download this PBIX file with code

This code will do the trick

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nT0VTAyMjBVitWJVgp2DFDw81cwMjAyUvCMtFSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Input", Splitter.SplitTextByCharacterTransition( {" "},  {"0".."9"})),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Input.2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Input.2.1", "Input.2.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter1",{{"Input.2.2", "Comments"}, {"Input.2.1", "Output"}})
in
    #"Renamed Columns"

Your result table above had IY9 in a Comments column so I wasn't sure if you wanted to keep that.  If you don't, just delete the column. 

Regards

Phil

[Anonymous]

Hi Phil,

Appreciate your reponse.

However the code breaks when I add more strings. See below.

Hence I was looking for a more robust regex expression that would not fail in the future

Input:

Output:

If you could also explain the expressions you use, that would be a cherry on the cake!

[PhilipTreacy]

Hi @Anonymous  @Anonymous 

If you just want the 4 digit number then this works.

Download sample PBIX file

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nT0VTAyMjBVitWJVgp2DFDw81cwMjAyUvCMtISLRUSAxcB8sGRUlAJQXCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Middle([Input],Text.PositionOfAny([Input],{"0".."9"}),4))
in
    #"Added Custom"

The code uses Text.PositionOfAny to find the first occurrence of a numeric digit in the text.  This is fed into Text.Middle to extract the 4 characters from that point, giving you the 4 digit number.

Regards

Phil

[Anonymous]

Hi Phil sorry for the late reply.

However the number can be as 1 digit and as long as 9 digits as well.

Its not the year, if you were confused by 2022