the name of column with max value in per row

[Anonymous]

Hi Folks

I am looking for a solution that would help me nested IF statements in M, which is not too bad but I was wondering if there is another way of doing it. 

So I have a table like this 

Column 1	Column 2	Column 3
2	0	5
-3	-5	-10
4	1	3

Then I created a new Column which determines the Max value out of these 3 columns 

Column 1	Column 2	Column 3	Max Value
2	0	5	5
-3	-5	-10	-3
4	1	3	4

What I need is a solution like this,  which will tell me the name of the column with the highest value 

Column 1	Column 2	Column 3	Max Value	Max Column Name
2	0	5	5	Column 3
-3	-5	-10	-3	Column 1
4	1	3	4	Column 1

any ideas? 

Thank you for sharing your knowledge 

[camargos88 · Accepted solution]

Hi @Anonymous ,

Add an index column and this code for a custom column:

let _index = [Index] in
"Column " &
Number.ToText(
Table.Max(
Table.AddIndexColumn(
Table.Transpose(
Table.RemoveColumns(
Table.SelectRows(#"Added Index", each [Index] = _index),
"Index")
),
"Column", 1,1),
"Column1")[Column]
)

[blablabla2345]

Hi!

I need help, can you please tell me how to make this third column with max value in numbers. 

Thanks a lot!

[Anonymous]

Hi 

I forgot to mention one factor .. in case multiple columns include the same value, let's say 0. 

What is the best way to control which Column Name will be picked? 

Or is it, therefore, better to use the nested IFs solution where based on the order of the IF Statements in the code it will pick the first argument that hits the max.?

Thank you for your thoughts and knowledge 

Column 1	Column 2	Column 3	Max Value	Max Column Name
0	-7	0	0	???

[dax · Accepted solution]

Hi @Anonymous , 

You also could refer to below M code 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUTIAYlOlWJ1oJV1jIFPXFEQYGoBFTIBsQyA2BvNAKsFqgJKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t, #"Column 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", Int64.Type}, {"Column 2", Int64.Type}, {"Column 3", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Index", Order.Ascending}, {"Value", Order.Descending}, {"Attribute", Order.Ascending}}),
    #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index.1", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index1", {"Index"}, {{"max", each List.Max([Value]), type number}, {"all", each _, type table [Index=number, Attribute=text, Value=number, Index.1=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all],"a",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Attribute", "a"}, {"Attribute", "a"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"all"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each ([a] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"a"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Index"}, #"Unpivoted Columns", {"Index"}, "Filtered Rows", JoinKind.RightOuter),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Queries",{{"Attribute", "max column"}}),
    #"Expanded Filtered Rows" = Table.ExpandTableColumn(#"Renamed Columns", "Filtered Rows", {"Attribute", "Value"}, {"Attribute", "Value"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Filtered Rows", List.Distinct(#"Expanded Filtered Rows"[Attribute]), "Attribute", "Value", List.Sum),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns2"

Best Regards,
Zoe Zhi

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

[camargos88 · Accepted solution]

Hi @Anonymous ,

Add an index column and this code for a custom column:

let _index = [Index] in
"Column " &
Number.ToText(
Table.Max(
Table.AddIndexColumn(
Table.Transpose(
Table.RemoveColumns(
Table.SelectRows(#"Added Index", each [Index] = _index),
"Index")
),
"Column", 1,1),
"Column1")[Column]
)