Dynamic comparison of different values

[L70F]

Hi, 

I hope someone can help me to get it working in Power Query.

I have two tables of items with old and new prices depended on different quantities.

The number of given combination “From Qty”<=>”Price Qty” might vary both for different items in the same table and for the same items in the different tables.

I have also one more table with  Annual_DMD and Order_Qty for the same items.

Wished result is table with qty and prices closest to Order_qty to evaluate price changes.

On each item level should:

1. value “From Qty_x” compares with value of ORDER_QTY.
   
   If value “From Qty_x” is the same or closest below “ORDER_QTY”, then this value and “Price qty-x“ are chosen to the result table.
2. 
   If such value doesn’t exist, chose “From Qty_x” closest above “ORDER_QTY”. This value and “Price qty-x“ goes to the result table.
3.  Calculate the “Old price volume” = “Price QTY_Old” * ”ANNUAL_DMD” and
   “New price volume” = “Price QTY_New” * ”ANNUAL_DMD”
   
   Is it possible to show the “Total SUM” of this columns in the upper row?

 4. Culculate the % of price change: “% price”= (“New price volume” - “Old price volume”)/ (“Old price volume”)

Thanks a lot and best regards

[Greg_Deckler]

This sounds like a good one for @ImkeF...

[ImkeF · Accepted solution]

The techniques described in these articles should get you started: 

https://www.powerquery.training/portfolio/vlookup-true-in-power-query/

http://ozdusoleil.com/2018/03/01/vlookup-true-equivalent-in-power-query-with-dynamic-tiers/

[L70F]

Dear Imke,

thank you for sharing this tips. It looks like a great solution, but unfortunataly it doesn't help my problem due I have need to go along the row to find the right value. To do transforming to the list is not good idea because the each item has several values and my project can consist of several thousand of items.

BR//Larissa

[L70F]

Hi Imke, it was quite complecated to come to the desired result with your suggestion, but I succed to the end. If there is no other more easy way to solve this I accept it as solution.

BR//Larissa

[adambhappy]

Let's assume it's 100 000 items and each item has five columns, this doesn't come out to more than 500 000 rows which is nothing in a PBI model so you should be afraid to unpivot the data if that makes working with the data simpler. Because it's a column store the number of rows has little importance, what matters is the cardinality of columns.