How to return the column name based on max value

[ah12]

Hi all, 

my first post is a begging post but as I'm in love with Power Query and Power Pivot I will try to be back to help others in the future !  Right now, I've failed to find a solution after a lot of googling.

I have thousands of rows/records with columns for date, ID, etc and also 50 columns with decimal values (in Power Query first, then will be loaded in to Power Pivot) - I have found the max value of the values in the 50 columns for each row, by using List.Max.

I now need to know WHERE the max value appeared - so I need to return the column name for the max value in each row.  I tried adding a calculated column and the closest I got was using Table.ColumnNames but got an error message and I'm not sure what to do next,

Thanks in advance

Andrew

[v-yulgu-msft · Accepted solution]

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

[ah12]

Hi @v-yulgu-msft

Thank you very much. I didn't find that clumsy at all - especially as I still do so much using the ribbon buttons. And it goes to show how powerful Power Query can be without writing M. I found a function called Record.FieldNames when I was trying to solve it but I need to develop my knowledge a bit more to actually use it.

Thanks again. 

[ah12]

Maybe I should have said, I don't mind if I do this in power query when I am loading the data, or in power pivot when I'm building the data model. Thanks again. 

[ah12]

I'm not going to mark this as solved as, technically, I didn't solve it by returning the column name where the MAX value was found.  But as I was desperate, I did a temporary fix using an IF conditional column with 50 if-else cases.  It wasn't pretty but it works.

I'm still really keen to find the solution to my original question though, as much as a developmental thing, and because I won't be able to sleep easily ;/