Excel Forum

How to get minimum value with condition like MINIFS formula in Power Query?

karthikumar sakthivel

Active Member

Members

Forum Posts: 4

Member Since:
May 31, 2021

Offline

May 31, 2021 - 9:21 pm

Hi All,

I am new to power query. I would like to get the minimum value with condition using power query. However, I am not able to find the solution.

For example, Cheapest price for the given product needs to be found for that specific current month. For the first moth (Jan), we cant find the minimum value. The condition is Product specific and we need to compare the price of the product in previous months only not with current month.

Herewith I have given the table that contains the cheapest price which I want to do using power query. Is it possible?

Product	Month	Price	Cheapest price (Minimum value with conditino like MINIFS)
A	Jan	50	50
A	Feb	45	50
A	Mar	30	45
A	Apr	40	30
A	May	100	30
B	Jan	15	15
B	Feb	20	15
B	Mar	25	15
C	Jan	100	100
C	Feb	89	100
C	Mar	85	89

Request you to provide your inputs.

Please let me know if you need more details in this regard.

Thank you,

Regards,

Kumar

See Answer

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

June 2, 2021 - 4:59 pm

Hi Kumar,

Can you provide some sample data? The data you have provided looks like the desired output, not initial data.

You should be able to do that by Grouping, (Transform>Group By year and month), with the Min function.

Add 2 index columns, one starting from 0 and 1 starting from 1, merge the table with itself, this will create the offset that brings the previous month minimum.

Answers Post

karthikumar sakthivel

Active Member

Members

Forum Posts: 4

Member Since:
May 31, 2021

Offline

June 3, 2021 - 5:14 pm

Hi Catalin Bombea,

Thank you so much for providing your solution.

As you suggested, I have attached the sample data and output data which I want.

Kindly find the attachment.

If possible, Could you please generate the required output using PowerQuery?

Thank you,

Regards,

Kumar

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

June 4, 2021 - 5:03 am

Here is an example.

karthikumar sakthivel

Active Member

Members

Forum Posts: 4

Member Since:
May 31, 2021

Offline

June 4, 2021 - 4:53 pm

Hi Catalin Bombea,

Thank you for generating the output.

Please note that the condition is getting the minimum value from all previous months and product specific.

Generated output is giving or verifying the previous month only not all previous months.

If you refer my initial table, as of May month for A product, cheapest price is 30 which belongs to March month.

Could you please generate the output with all previous months (the range needs to be selected from the initial price to previous month's price for getting the minimum value)?

Thank you,

Regards,

Kumar

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

June 5, 2021 - 12:51 pm

Try this one.

karthikumar sakthivel

Active Member

Members

Forum Posts: 4

Member Since:
May 31, 2021

Offline

June 6, 2021 - 4:11 am

That's awesome. It works really cool. Thank you Catalin Bombea for your wonderful support.

I am able to get the output using your suggested method. Thanks a lot!!!

Regards,
Kumar

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

June 6, 2021 - 12:15 pm

you're welcome

Forum Timezone: Australia/Brisbane

Most Users Ever Online: 245

Currently Online:

Guest(s) 9

Currently Browsing this Page:
1 Guest(s)

How to get minimum value with condition like MINIFS formula in Power Query?|Power Query|Excel Forum|My Online Training Hub