Forum Discussion
mateoc15
Advocate II
7 months agoFiltering 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 ma...
- 7 months ago
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.
jgeddes
Super User
7 months agod_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