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

0 votes

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 by Kithuzzz
• 38,010 points
409 views

1 answer to this question.

0 votes

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

answered Feb 6, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

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

answered Dec 19, 2022 in Others by narikkadan
• 63,420 points
1,181 views
0 votes
1 answer

How do I reference the cell value within a formula string?

Try: =HYPERLINK("#"&CELL("address",D116),B37) READ MORE

answered Feb 24, 2023 in Others by narikkadan
• 63,420 points
580 views
0 votes
1 answer

How do I stop python from appending data to the same row in excel?

There is no indication in your code ...READ MORE

answered Mar 25, 2023 in Others by narikkadan
• 63,420 points
292 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

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

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
909 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

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

answered Oct 3, 2018 in Python by Priyaj
• 58,090 points
832 views
0 votes
1 answer

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

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

answered Oct 15, 2018 in RPA by Priyaj
• 58,090 points
4,096 views
0 votes
1 answer

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

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
3,848 views
0 votes
1 answer

In excel how do I reference the current row but a specific column?

Put a $ symbol in front of ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 63,420 points
1,361 views
0 votes
1 answer

How do I get it to select a single row based on the value?

Try this: Sub MoveBasedOnValue2() Dim ...READ MORE

answered Jan 20, 2023 in Others by narikkadan
• 63,420 points
381 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP