Find Max Value / Max String based on Multiple columns

Hi KR300,

 

See if one of these approaches does the trick for you.

 

let
    Source = Table.FromRows(
        {
            {"1", "SFP.R.11.9", "SFP.R.11.8", null, null}, 
            {"2", "SFP.R.11.7", "SFP.R.11.8", null, null}, 
            {"2", "SFP.R.11.8", "SFP.R.11.9", null, null}, 
            {"3", "SFP.R.11.7", "SFP.R.11.8", "SFP.R.11.9", "SFP.R.11.10"}, 
            {"3", "SFP.R.11.8", "SFP.R.11.9", "SFP.R.11.10", "SFP.R.11.11"}, 
            {"3", "SFP.R.11.9", "SFP.R.11.10", "SFP.R.11.11", "SFP.R.11.12"}, 
            {"3", "SFP.R.11.10", "SFP.R.11.11", "SFP.R.11.12", "SFP.R.11.13"}
        }, 
        type table [Issue ID = Int64.Type, FixVersion = text, FixVersion2 = text, FixVersion3 = text, FixVersion4 = text]
    ), 
    Custom1 = Table.AddColumn( Source, "Max String", 
        each List.First(
            List.Sort(
                List.RemoveMatchingItems(List.Skip(Record.ToList(_)), {null, ""}), 
                {(x) => Number.From(List.Last(Text.Split(x, "."))), Order.Descending}
            )
        ), type text
    ), 
    Custom2 = Table.AddColumn( Source, "Max String", 
        each List.Last( 
                List.Sort(
                    List.RemoveMatchingItems(Record.FieldValues(_), {null, ""}), 
                    (x, y) => Number.FromText(List.Last(Text.Split(x, "."))) - Number.FromText(List.Last(Text.Split(y, ".")))
                )
            ), type text
    )
in
    Custom2

 

 

I hope this is helpful

Here is another way to solve. 

let
    Source = 
    Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQp2C9AL0jM01LNA5lgCOSAUqxOtZIQsYY5XC5xjaICp1QJqJjZj0TUj8wxhOozRHYJkmjEem4yx+QybHLLFRnoooWOI4lxDY6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Issue ID" = _t, FixVersion = _t, FixVersion2 = _t, FixVersion3 = _t, FixVersion4 = _t]),
    changeTypes = 
    Table.TransformColumnTypes(
        Source,
        {
            {"Issue ID", Int64.Type}, {"FixVersion", type text}, {"FixVersion2", type text}, {"FixVersion3", type text}, {"FixVersion4", type text}
        }
    ),
    addMaxColumn = 
    Table.AddColumn(
        changeTypes, 
        "Max FixVersion", 
        each 
        Text.AfterDelimiter(
            List.Max(
                List.Transform(
                    List.RemoveFirstN(
                        Record.ToList(_), 
                        1
                    ), 
                    each fxPadVersionText(_)&"|"&_
                )
            ), 
            "|"
        ), 
        type text
    )
in
    addMaxColumn

and the function code for fxPadVersionText()

(inputValue as text)=>
let
    childValue = Text.AfterDelimiter(inputValue, ".", {0, RelativePosition.FromEnd}),
    parentValue = Text.BetweenDelimiters(inputValue, ".", ".", {0, RelativePosition.FromEnd}, {0, RelativePosition.FromEnd}),
    prefixValue = Text.BeforeDelimiter(inputValue, ".", {1, RelativePosition.FromEnd})&".",
    paddedValue = prefixValue & Text.PadStart(parentValue, 4, "0") & "." & Text.PadStart(childValue, 4, "0")
in
    paddedValue

This method should allow the sorting to work if the parent value (11) changes as well.

Cheers.