Search code examples
excelexcel-formulapivot-tablepowerqueryexcel-tables

Specifying Which Column a Max Value Came From in an Excel Table

My example table

Part Number 00 01 02 03 04 EX Max Value
0006321 0 0 -1 0 1 0 1
0007244 7 8 0 0 0 0 8
0015600 24 0 0 0 0 0 24
0016301 0 0 0 0 0 0 0
0017004 -4 2 2 0 0 0 2
0019440 0 0 0 1 0 0 1
0020639 -4 0 5 0 0 0 5
0023448 0 6 0 0 0 0 6
0025299 -2 0 2 0 0 0 2
0025743 0 -1 0 0 0 0 0
0026453 0 0 2 0 0 0 2
0026454 -3 1 2 0 0 0 2

In the image posted above the headers 00, 01, 02, 03, 04, EX represent different stores and the values are the excess quantity of each part number at that store. As you can see, I was able to get the max value of excess quantity out of all the stores, but I would also like to show in the column next to it which store that value is associated with. My ultimate goal is to create a dataset showing which store has the highest excess quantity and how much the excess quantity is. I am unsure if there is a formula for this or possibly a pivot table function that could help me out. I am also familiar with Power Query if that could also help in some way. Thank you!

Solution

  • How about below for Excel. See screeenshot

    enter image description here

    powerquery version

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Max", each List.Max(List.RemoveFirstN(Record.ToList(_),1)) ),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "ColumnMax", each Table.ColumnNames(Source){List.PositionOf(Record.ToList(_),[Max])})
in  #"Added Custom1"