Conditional Ranking as Calculated Column or Power Query

[hamachekm]

I'm trying to add a ranking to a table. In this simple example, I want to rank the cost of each Fruit. This ranking will then be used to pull in a value from another table, so I want to do this either in Power Query or as a Calculated Column. (In Excel, I'd use =SUMPRODUCT((C:C<=C4)*(A:A=A4)) )

Thanks - Mike

Fruit	State	Price	Fruit Rank
Apple	WI	100	1
Apple	TN	500	4
Apple	CA	200	2
Apple	WA	300	3
Pear	WI	200	2
Pear	CA	100	1
Pear	TX	400	3

[CNENFRNL · Accepted solution]

@hamachekm , you might want to refer to the following solutions in Power Query and in calculated column,

Power Query solution

Calculated column solution,