How do I output the row containing the lowest value from a specific column inside a filtered dynamic array?

Question

I have a list of suppliers which contains the product name and the prices. I then formatted the data where there are multiple entries of the product name but under different suppliers. For each product, I need the lowest price and under what supplier I can get that lowest price from.

I don't know if there's an easier method through power query but the solution I used is using FILTER.

=FILTER(DATA,CRITERIA)

ITEM	SUPPLIER	PRICE
ITEMA	SUPPLIERA	100
ITEMA	SUPPLIERB	150
ITEMA	SUPPLIERC	125
ITEMB	SUPPLIERA	100
ITEMB	SUPPLIERB	150
ITEMB	SUPPLIERC	125

What I tried: I was able to create a FILTER function to output the suppliers and price based on the item I need. Through searching the net, I don't know how to get the minimum number from the filtered list itself.

Output I was able to get through the function: UNIQUE(TABLE[ITEM])|FILTER(TABLE,A1=TABLE[ITEM])

A	B	C
ITEMA	SUPPLIERA	100
ITEMA	SUPPLIERB	150
ITEMA	SUPPLIERC	125

Since the output is a dynamic array, I don't know how to "read" the 3rd column of this array to get the minimum value.

Answer 1

Solved it.

Instead of using 1 filter function to output the whole row, I used 2 filter functions.

First filter function gets the minimum value with the selected criteria: MIN(FILTER(TABLE[PRICE],A1=ITEMA))

Second filter function outputs the row with the minimum value: FILTER(TABLE,B1=TABLE[PRICE])

So my dynamic table looks like |A|B|C| |-|-|-| |ItemA|=MIN(FILTER(TABLE[PRICE],A1=ITEMA))|=FILTER(TABLE,B1=TABLE[PRICE])|

A	B	C	D
ItemA	100	SupplierB	100

For clarity sake, I decided to leave the extra 100 there but there is a way to output filter only specific columns and that's what I used