Forum Discussion
Filtering text with > or <
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!
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.
5 Replies
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.
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.
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_indexmateoc15
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 result2. 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.
