How to return the column name based on max value

Hi ah12,

I didn't find a Power Query function that can directly return a column name. Maybe you could try below clumsy workaround.

1. Add a index column.

2. Choose [Index] column then unpivot other columns to change table structure to below:

3. Get the max value in each row grouped by index.

4. Expand the expansion. And add a custom column to return relative column name whose value is max.

5. Remove unnecessary columns, then, pivot table.

6. Please refer to this thread to concatenate grouped values.

Below is the M code for your reference:

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\Desktop\Sample Data.xlsx"), null, true),
    #"Get Column Name_Sheet" = Source{[Item="Get Column Name",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Get Column Name_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Index"}, {{"Max Per Row", each List.Max([Value]), type number}, {"expansion", each _, type table}}),
    #"Expanded expansion" = Table.ExpandTableColumn(#"Grouped Rows", "expansion", {"Attribute", "Value"}, {"expansion.Attribute", "expansion.Value"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded expansion", "Relative Column", each if [expansion.Value] = [Max Per Row] then [expansion.Attribute] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Max Per Row"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[expansion.Attribute]), "expansion.Attribute", "expansion.Value")
in
    #"Pivoted Column"

Best regards,

Yuliana Gu