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 vendors, together with the names of the products and their costs. The data that had numerous entries for the same product name but under various providers was then formatted. I need to know the lowest price for each product and from which suppliers I can acquire it.

I'm not sure if there is a simpler way to use a power query, but the approach I used involved utilising 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

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