Power Query (M Language) Count Duplicates within two dates

[dlukin778]

I have a table with a unique key that I would like to count. However, I only want to count the item IF it exists between two date columns. 

Logic: count if THIS unique key occurrs again between date opened and dateplus 7. 

Resulting code in M Language please. 

UNIQUE KEY              DATE OPENED               DATEPLUS7                         CUSTOM COLUM RESULT WANTED
B                                12/05/2022                   12/12/2022                          2  

B                                12/07/2022                   12/19/2022                          2

B                                12/13/2022                   12/20/2022                          1

B                                6/13/2022                      6/20/2022                           1

B                                4/13/2022                      4/20/2022                           1

[v-yalanwu-msft · Accepted solution]

Hi, @dlukin778 ;

Yes, that one really didn't work, you can change to:

let 
b=[#"DATEPLUS7 "]
in
Table.RowCount(Table.SelectRows(#"Changed Type",each 
([DATE OPENED]<=b and [#"DATEPLUS7 "]>=b) ))

 
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @dlukin778 ;

Try it custom column.

let 
a =[DATE OPENED],
b=[#"DATEPLUS7 "]
in
Table.RowCount(Table.SelectRows(#"Changed Type",each 
([DATE OPENED]<=b and [#"DATEPLUS7 "]>=b)

 ))

The final show:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclLSUTI00jcw1TcyMDKCcIAIzInVQcibI8tbYsgbGiPJGxmgypvBpBXAHHRpEyTdJgjZWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"UNIQUE KEY  " = _t, #"DATE OPENED" = _t, #"DATEPLUS7 " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UNIQUE KEY  ", type text}, {"DATE OPENED", type date}, {"DATEPLUS7 ", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let 
a =[DATE OPENED],
b=[#"DATEPLUS7 "]
in
Table.RowCount(Table.SelectRows(#"Changed Type",each 
([DATE OPENED]<=b and [#"DATEPLUS7 "]>=b)

 )))
in
    #"Added Custom"

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dlukin778]

Thanks for the answer. 
I'm currently 
I noticed that you have two definitions. A and B. But A is never used for some reason. Am I missing something?