cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ah12 Frequent Visitor
Frequent Visitor

How to return the column name based on max value

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: 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.

1.PNG 

 

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

2.PNG

 

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

3.PNG

 

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

4.PNG

 

5. Remove unnecessary columns, then, pivot table.

5.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
ah12 Frequent Visitor
Frequent Visitor

Re: How to return the column name based on max value

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 Frequent Visitor
Frequent Visitor

Re: How to return the column name based on max value

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 ;/

 

 

Community Support Team
Community Support Team

Re: 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.

1.PNG 

 

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

2.PNG

 

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

3.PNG

 

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

4.PNG

 

5. Remove unnecessary columns, then, pivot table.

5.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ah12 Frequent Visitor
Frequent Visitor

Re: How to return the column name based on max value

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.