Find Max Value / Max String based on Multiple columns

[KR300]

Hi team, I want to find the ax Value or string based on the multiple columns ( Except Issue ID Column ). Pls see the below screenshot

Note: It will include Nulls or Blanks or all Strings, anything is poosible those cell but to show Max String /  Max Value ( No Nulls or Blanks in the result Column )

[m_dekorte · Accepted solution]

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

[dufoq3]

Hi @KR300, another solution:

Output

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]
    ),
    Ad_MaxFixVersion = Table.AddColumn(Source, "MaxFixVersion", each 
        [ rec = List.Skip(Record.ToList(_)),
          a = List.Zip({ List.Positions(rec), rec }),
          b = List.Select(a, (x)=> not List.Contains({null, ""}, Text.Trim(x{1}))),
          c = List.Transform(b, (x)=> {x{0}, Text.Combine(List.Transform(Text.Split(Text.Range(x{1}, Text.PositionOfAny(x{1}, {"0".."9"})), "."), (y)=> Text.PadStart(y, 4, "0"))) }),
          d = List.Select(c, (x)=> x{1} = List.Max(List.Transform(c, (y)=> y{1}))){0}{0},
          e = rec{d}
        ][e], type text)
in
    Ad_MaxFixVersion

[jgeddes · Accepted solution]

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.

[m_dekorte · Accepted solution]

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