Power Query - Custom Col Based on 2 Conditions (Date & Product ID) RRS feed

  • Question

  • i All


    Please I need some help to create a custom column in Power query to show events based on rows with Today and the past 7 days. 

    The main criteria I wish to have is to base on identify the product with today's date and also appearing in the past 7 days. In the image below, Product 1 meets these requirement and Product 2 & 3 do not as it does not have any rows with today's date. 


    Please see example of what I intend to do below. I would really appreciate any help I can get. Thank you all for reading this and I look forward to any advice you might have for me.


    The current work around is I copy today's products onto to a different table then have the formulas shown below pointing to that table. 

    Helper Column - D

    =IF(ISNA(VLOOKUP([@[Product ID]],TodayProducts[Product ID],1,FALSE)),1,COUNTIF(C:C,C2))

    The formula in the Helper column is a logical test check if the product ID in Column C is also on the Today's table, if this product ID is not found then false returns 1, However if true then the formula counts all the product IDs in Col C. 


    Col E 

    =IF([@Date]=TODAY(),"Yes",IF([@[Helper Column]]>1,"Yes","No"))


    Combines those product ID's with today's date and those with greater than 1 in the helper column, these are the products we are interested in on our report. 


    Please could you advise if this possible in Power query or any other formulas which can be added to the custom column so I do not have to manually add today's products in a separate table every day.

    • Edited by Ronnie Cee Monday, June 4, 2018 11:12 PM
    Monday, June 4, 2018 10:51 PM


All replies