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”?
Thanks
Roberto
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
Roberto
