Excel match and index in Power Query

Hi @sam.mckay,
I got something I was used to doing in Excel, but I really don’t where to start in Power Query.
I have a list of values (3 in this case) in three columns Alpha, Bravo, Charlie.
I need the column name of the maximum value for that row and the maximum value.
I can do it in DAX with some switch instruction, but I think Power Query is the better place.
After I get the max value is there any other option than nested “if then else”?



match_index.xlsx (21.2 KB)

Hi @Roberto

Can you please show what output you are looking for?

Hi @Roberto,

To get the Max Value you could use: List.Max( Record.ToList(_))

How does it work?
Record.ToList(_) turns the current row values in a List
List.Max then extracts the maximum value


And to get the Name: Record.FieldNames(){List.PositionOf(Record.FieldValues(), [Max Value])}

How does it work?
Record.FieldNames() returns a list of all the column headers
To get a single Name from that List, you can use the position index operator. For example adding {0} would extract the first position in the List. So inside that access operator we lookup the position of that Max Value using this syntax: List.PositionOf(Record.FieldValues(_), [Max Value])

Here is your sample file: eDNA - Excel Index and Match.pbix (20.7 KB)
I hope this is helpful


Thank you @Melissa! This is another proof that using only UI to generating code is spoiling me. M is full of power functions indeed. Thanks again


Hi @MK3010,
thanks for your question.
Please check @Melissa answer, that’s the solution I was looking for.



1 Like