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.
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