How to write COUNTIFS function in Power Query where 2nd piece of COUNTIF is searching for 7 days ahead of date value

Question

I wrote a COUNTIFS function in Excel to tell me if there is a date a week away from the indexed row date, including one more criteria. The function reads as:

COUNTIFS(Table1_1[Lookup2],Table1_1[@Lookup2],Table1_1[Date],Table1_1[@Date]+7)

where Lookup2 is a helper column and Date is a date

How would I do this in PowerQuery?

I have tried Group By function, but don't know how to add the "+7" days piece to it

Lookup1	Date	Result
AC - ALBERTSONSCO ACME - RMABEAR NAKED11	1/22/2023	1
AC - ALBERTSONSCO ACME - RMABEAR NAKED11	1/29/2023	1
AC - ALBERTSONSCO ACME - RMABEAR NAKED11	2/5/2023	0

The above is an example - since there is a date 7 days after 1/22/2023, given the Lookup1 criteria, it will return a 1. There is no date within 7 days of 2/5/2023 with Lookup1 criteria, so it returns a 0

Answer 1

Try adding a Custom Column with:

Table.RowCount(
  Table.SelectRows(
    PreviousStep, (tbl) => 
      tbl[Lookup1] = [Lookup1] and 
      tbl[Date] > [Date] and 
      tbl[Date] <= Date.AddDays([Date], 7 ) 
  )
)

Do update PreviousStep with the name of your #"Previous step".