Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

the name of column with max value in per row

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 1Column 2Column 3
205
-3-5-10
413

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

 

Column 1Column 2Column 3Max Value
2055
-3-5-10-3
4134

 

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

 

Column 1Column 2Column 3Max ValueMax Column Name 
2055Column 3
-3-5-10-3

Column 1

4134Column 1

 

any ideas? 

 

Thank you for sharing your knowledge 

2 ACCEPTED SOLUTIONS

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]
)

 

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

dax
Community Support
Community Support

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.

 

 

View solution in original post

4 REPLIES 4
blablabla2345
New Member

Hi!

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

 

Thanks a lot!

Anonymous
Not applicable

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 1Column 2Column 3Max ValueMax Column Name
0-700???
dax
Community Support
Community Support

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.

 

 

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]
)

 

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors