Extract a pattern of type X.Y or X.Y.Z or X.Y.Z.T in a character string where X,Y,Z,T are numbers.

[Pentester]

Hello community,

I would like to extract the software version in a field containing the software name and the version in X.Y or X.Y.Z or X.Y.Z.T format. Could anyone help me?

Below is an example of data and the expected result.

NAME	VERSION
Notepad 3.95	3.95
Kali 2024.1 (64 bit)	2024.1
Microsoft .NET Runtime - 6.0.9 (x64)	6.0.9
Microsoft Visual C++ 2008 Redistributable - x64 9.0.30729.17	9.0.30729.17
Microsoft SQL Server 2016

[dufoq3]

Hi @Pentester, single step solution:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xcy9DoIwGIXhWznphEGb8mOxs3FSSQTjQhhaqMmXoJhSjJdvdWQ+532ahpWjty/dI+Nqy9p1w456IKQizXmCSOYw5Ff/4UydG6fx7sHLwxXV/PT0sNhAcsEVoo/Ml8cbTbMesI/jIIodKtvT5B2Z2Wsz/NoQQYU+E0WqeFIsgPpyQm3d27oAJJK17Rc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [NAME = _t]),
    Ad_Version = Table.AddColumn(Source, "Version", each
      [ a = Splitter.SplitTextByCharacterTransition((x)=> not List.Contains({"0".."9"}, x), (y)=> not List.Contains({"0".."9"}, y))([NAME]),
        b = List.Transform(a, (x)=> Text.Trim(x)),
        c = List.Select(b, (x)=> List.ContainsAny(Text.ToList(x), {"0".."9"}) and Text.Contains(x, ".")),
        d = List.TransformMany(c, (a)=> {List.Transform(Text.Split(a, " "), (b)=> Text.ToList(Text.Lower(b)))} , (x,y)=> List.Transform(List.Select(y, (c)=> not List.ContainsAny(c, {"a".."z"})), Text.Combine){0}? ){0}?
      ][d], type text)
in
    Ad_Version

[Syk · Accepted solution]

First, lets duplicate the column. Right click > Duplicate Column

Now we will split the new column by a demiliter at each occurence (space). Transform > Split column > by delimiter

Now we have a number of columns with junk but some of them will have the version.

Select your name column and Transform > Unpivot Columns > Unpivot Other Columns

Then filter your value column to include a period but not include NET

And you're left with just the version!

[Pentester]

Thank you very much SyK. It perfectly meets my needs.