Filtering text with > or <

[mateoc15]

I have software version information like version 11.0.2 and 11.0.10.  If I want to filter to all versions 11.1.0 or later I don't see a good way to filter.  Yes, I have broken it into three pieces major, minor, and patch, but for these two version examples when sorting as text 11.0.10 is actually earlier than 11.0.2 (as a string "10" < "2")

My solution was to left pad each piece to 3 characters so that the version looks like 011.000.010 and 011.000.002 which works great for sorting, but not for filtering because I don't see a > or < operator, only for numeric values, only things like "contains" and "blank", etc.

So what's my best option?  Thanks!

[FreemanZ]

hi @mateoc15 ,

How about convert the version number further to an integer like:
011.000.010 => 11000010
major*10^6 + minor * 10^3 + patch

[pcoley]

@mateoc15 
you can create a formula in powerquery: 
1. paste the next code in the advance editor:

(myText as text) as number =>
let
  parts = Text.Split(myText, "."),
  numbers = List.Transform(parts, each Number.FromText(_)),
  count = List.Count(numbers),
  exponents = List.Transform({0..count-1}, each 8 - 2 * _),
  products = List.Transform({0..count-1}, each numbers{_} * Number.Power(10, exponents{_})),
  result = List.Sum(products)
in
  result

 2. in your table create a new column invokating this function:

with this colum you can compare the codes as integer numbers. 

If this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way faster.

[jgeddes]

@d_m_LNK  has given an easy solution that should work.
Here is more complicated Power Query solution if you are in to that sort of thing...

let
    Source = 
    Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "i45WMjTUM9AzUorVgTINDWBsIA/BRBaGqjZCUm2EMMQIojEWAA==", 
                    BinaryEncoding.Base64
                ), 
                Compression.Deflate
            )
        ), 
        let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Version = _t]
    ),
    // turn table into nested list
    nested_list = 
    List.Transform(
        Table.ToList(Source), 
        each Text.Split(_, ".")
    ),
    // sort the list
    sort_list = 
    List.Sort(
        nested_list, 
        (x,y)=> 
            if Value.Compare(Number.From(x{0}), Number.From(y{0})) <= 0
                then
                    if Value.Compare(Number.From(x{1}), Number.From(y{1})) <= 0 
                        then Value.Compare(Number.From(x{2}), Number.From(y{2}))
                        else 1 
                else 1
    ),
    // convert list to table
    convert_to_table = 
    Table.FromList(
        List.Transform(sort_list, each Text.Combine(_, ".")), 
        Splitter.SplitByNothing(), 
        {"Version"}, 
        null, 
        ExtraValues.Error
    ),
    // set data type
    set_types = 
    Table.TransformColumnTypes(
        convert_to_table,
        {{"Version", type text}}
    ),
    // add a sort order index
    add_sort_order_index = 
    Table.AddIndexColumn(
        set_types, 
        "VersionSortOrder", 
        1, 
        1, 
        Int64.Type
    )
in
    add_sort_order_index

[d_m_LNK · Accepted solution]

I think the solution would to use power query to remove the decimal points and possilby store the verison number as a whole number in a column.  That way you are just dealing with numbers like, 1110, 1101 and 1102 as the trailing zero shouldn't matter for the comparison you are trying to make.

[mateoc15]

Because I'm only concerned about a version 11+ that should be alright, two digits there is good.  Won't cause a problem until we get to version 99+, and I won't be around for that.  Thank you.