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.

Feb 6, 2023 in Others 409 views

## 1 answer to this question.

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

• 63,420 points

## How do I create a hyperlink from Word to Excel specific cell, without macros/vba?

All you have to do is copy ...READ MORE

## How do you populate a google sheets/excel column with cells from a column in another sheet in the same document?

You have two options on chronology: sheet-by-sheet =QUERY({Sheet1!X:Z; Sheet2!X:Z; ...READ MORE

## Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

Forget the PsychoPy complications for the time ...READ MORE

## In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

## Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE