Search code examples
maxpowerquerym

Which column has a max in Power Query table

There is a table:

ID | Value1 | Value2 | Values3
A  |      1 |      2 |       3
B  |      2 |      4 |       3
C  |      3 |      2 |       1
D  |      1 |      2 |       3

Is there a function or set of functions returning which column has the max value?

ID | Value1 | Value2 | Values3 | Max_in
A  |      1 |      2 |       3 | Values3
B  |      2 |      4 |       3 | Values2
C  |      3 |      2 |       1 | Values1
D  |      1 |      3 |       3 | Values2
or
D  |      1 |      3 |       3 | Values2;Values3
Solution

  • To do this without unpivoting, write a custom column like this:

    = Table.AddColumn(
    #"Changed Type",
    "Max_In",
    (r) =>
        Text.Combine(
            List.Select(
                {"Value1", "Value2", "Value3"},
                each Record.Field(r, _) = List.Max({r[Value1], r[Value2], r[Value3]})
            ),
            ";"
        ),
    type text
)

    Full query you can past into the advanced editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYiMgNlaK1YlWcoLyTOAizmAWRNQQLOKCocsVKmIMEYkFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Value1 = _t, Value2 = _t, Value3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value1", Int64.Type}, {"Value2", Int64.Type}, {"Value3", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Max_In", (r) => Text.Combine(List.Select({"Value1", "Value2", "Value3"}, each Record.Field(r, _) = List.Max({r[Value1], r[Value2], r[Value3]})),";"), type text)
in
    #"Added Custom"

    Screenshot