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
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.
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
Here is an example.
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
Try this one.
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
you're welcome
