How to take Max String value based on Another Column which contains same values.

Hey!

You can try an variation on this code. Som additional code may be needed to clean your data before.

The code below code does the following:

1. use a custom function to create a numeric combined value from main and sub version.
2. Group the rows on issue to get the max
3. Filter the rows so only the value that equals max will remain. 

let
    Source = YOURDATA,
    filterEmpty = Table.SelectRows(Source, each [FixVersion] <> null and [FixVersion] <> ""),

    fn_VersionCombined = (vVersion as text) =>
    let
        split = Text.Split(vVersion, "."),
        main = split{2},
        sub = split{3},
        outcome  = Number.From(Text.Combine({main, if Number.From(sub) <= 9 then "0" & sub else sub}))
    in
        outcome,
    
    invoke_fnVersionCombined = Table.AddColumn(filterEmpty, "VersionCombined", each fn_VersionCombined([FixVersion]), Int64.Type),
    GroupRows = Table.Group(invoke_fnVersionCombined, {"Issue ID"}, {{"Max", each List.Max([VersionCombined]), type number}, {"Table", each _, type table [Issue ID=nullable number, FixVersion=nullable text, VersionCombined=number]}}),
    ExpandTable = Table.ExpandTableColumn(GroupRows, "Table", {"FixVersion", "VersionCombined"}, {"FixVersion", "VersionCombined"}),
    FilterRows = Table.SelectRows(ExpandTable, each ([VersionCombined] = [Max]))
    
in
    FilterRows

Hopefully this is usefull, if so consider accepting it as a solution to help other users!

Good luck!

The code I supplied earlier seems to work with your new sample:

Source

Results

By the way, if you want to change the name of the resultant column, merely edit the last line of code before the "in":

Change:

Table.ExpandRecordColumn(#"Grouped Rows", "Latest Version", {"FixVersion"})

to something like:

Table.ExpandRecordColumn(#"Grouped Rows", "Latest Version", {"FixVersion"},{"MaxVersion"})

Hi,

Thanks for the solution KR300 , Chewdata , ThxAlot  and Ahmedx  offered, and i want to offer some more information for user to refer to.

hello KR300 , you can refer to the follwing code in advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBLCsAgDATQu2QtwbEf9QJdF7sU73+NfrDUMtk+EoaZWgXi5Nh2LQpokuaqhJHiQIWviu435YemkSJTYjIe4R+b+XNhWpkiU2LKTFcwGwwLhhlFYVTA1yH38j/q5QeDVxgWyN7JYcz0N8rtq7cT", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Issue ID" = _t, FixVersion = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Issue ID", Int64.Type}, {"FixVersion", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Select([FixVersion],{"0".."9"})),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Issue ID"}, {{"Max", each let a=List.Max([Custom])
in List.Select([FixVersion],each Text.Contains(Text.Remove(_,"."),Text.From(a))){0}, type nullable text}})
in
    #"Grouped Rows"

Ouptut

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

let
    result = Table.Group(
        source_table, 
        "Issue ID", 
        {
            "FixVersion", (x) => List.Max(
                x[FixVersion], 
                null, 
                (w) => ((nums) => Number.From(nums{0}) * 1000 + 
                    Number.From(nums{1})
                )(List.LastN(Text.Split(w, "."), 2))
            )
        }
    )
in
    result