Can I subtract used amounts based on the expiration date?... Using Power Query! RRS feed

  • Question

  • Hello, I'm trying to find a solution to my question but I'm running in circles and Information...  

    I have two tables:

    Table 1 (Production_Base_Table):

    Index Product Used Current Date Used
    1 A 898.74 11/11/2014
    2 A 908.6 12/1/2014
    3 A 100 7/6/2015
    4 A 300 7/20/2015
    5 A 110 10/12/2015
    1 B 10 11/11/2014
    2 B 30 12/1/2014
    3 B 300 12/15/2014
    4 B 1000 12/22/2014

    Table 2 (Inventory_Data):

    Index Part Number Current Value Expiration_Date
    1 A 910.09 2/28/2015
    2 A 550.33 5/31/2015
    3 A 400 6/30/2015
    4 A 5000 7/31/2015
    5 A 6000 8/31/2015
    6 A 100 10/31/2015
    1 B 600 5/31/2015
    2 B 900 6/30/2015
    3 B 200 7/31/2015

    Base on the Inventory of Table 2, we need to consume the Values used on the Table 1 and get a final balance:

    A) The consume should be first by the applicable product.
    B) The current Date for the Value used on the Table 1 should not be greater than the applicable Expiration date.
    C) If the subtraction of the current Value (Table 2) is greater than the value used (Table 1) the balance should be added to  the next applicable line in order to apply the consume to the next line. e.g.( 910.09 - 898.74 = 11.35  then the next value of 550.33 should be 561.68 (550.33+11.35).

    Thank you and I hope that some one can shed a light on this!!!

    Tuesday, January 23, 2018 12:21 AM


All replies

  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Show daily inventory for 6 months.
    Aggregate with M, not DAX.

    Wednesday, January 24, 2018 12:49 AM
  • Hi Herbert, I really appreciate your support and knowledge, I like the concept that you are using but I'm breaking my head in order to apply these to each individual Product Type (A, B... etc). The other point is that in some cases have expiration dates since 2014 and I need to create a Date List since 2014 with this concept?

    Thank you in advance

    Kind Regards,


    Thursday, January 25, 2018 1:46 AM
  • Changed to 2014/2015 dates and added parameters for product types.
    Recommended books:
    "M is for Data Monkey" by Puls and Escobar
    "Power Query for Power BI and Excel" by Chris Webb

    Thursday, January 25, 2018 8:41 PM
  • Thank you for the recommendations, one of the things that I have with this option is, that I'm using to many Product Types (50 or more) and changes these products at the way that are set will be difficult I think, is there any way to use some IF condition to define the subtractions... sorry I'm stuck


    Monday, January 29, 2018 8:27 PM
  • Expanded the Parameter1 list to 50 different Product Types
    and assigned them randomly to Table1/Table2.
    Upon refresh, everything worked as before, at the same speed.
    If you just want results, without thinking about it, share your full file, weekly.

    Tuesday, January 30, 2018 7:48 PM
  • Hello,

    I like to thinking about it in order to learn more, but in this case it goes out my hands since I couldn't the above results.... Jut to let you know, previously I get some help with a simple code to subtract quantities line by line. I was thinking that this similar code could support my request on the expiration Dates... If you have a chance please take a look on.



    Friday, February 2, 2018 5:35 PM
  • The "M" code you copied works well in its original context.
    But to adapt it to your data and structure
    you need to modify it extensively to account for your multiple products,
    the initial inventory and the desired time line.
    The tweaked code will then look almost the same as my code.
    Adapting someone elses code takes more knowledge of "M"
    than creating your own code from scratch.
    Happy to hear from you after a steep three months+ learning curve.

    Saturday, February 3, 2018 1:55 AM