Power Custom Col Based on 2 Conditions (Date & Product ID)

[Ronnie Cee]

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.

 

[horseyride]

This should do the trick

Start with a two column table named Table1 that uses the headers "Product" and "Date"
Use following code to find Product that has today's date and a date within the past 7 days

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Date", type date}}),
    Now = DateTime.Date(DateTime.LocalNow()),
    MinDate = Now -#duration(7,0,0,0),
    Filter= Table.SelectRows(#"Changed Type", each [Date] = Now),
    Filter2= Table.SelectRows(#"Changed Type", each [Date]>=MinDate and [Date]<Now),
    #"Merged Queries" = Table.NestedJoin(Filter,{"Product"},Filter2,{"Product"},"Table1",JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Date", "Table1"})
in
    #"Removed Columns"