How do I compare a date on one table to a date on another table in PowerQuery M

hi EaglesTony ,

 steps taken:

1. create a duplicate of the comparedate query.

2. drilled down in 2 times, once of the start date and once on the calcendate column of the comparedate query. First drill will created a list, second one will covert it into a datetime value

3. Filtered the other query based on the input from the compared date query.

 

 

create 3 blank queries and copy paste the below:

 

1.CompareDate_Start

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzbUNzIwNFHSUTIw0jc0g3BiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"START DATE" = _t, COMPLETE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"START DATE", type date}, {"COMPLETE", type date}}),
    #"START DATE" = #"Changed Type"[START DATE],
    #"START DATE1" = #"START DATE"{0}
in
    #"START DATE1"

 

 

2.  CompareDate_COMPLETE

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzbUNzIwNFHSUTIw0jc0g3BiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"START DATE" = _t, COMPLETE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"START DATE", type date}, {"COMPLETE", type date}}),
    COMPLETE = #"Changed Type"[COMPLETE],
    COMPLETE1 = COMPLETE{0}
in
    COMPLETE1

3. ActiveTable

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw0jc01jcyMDJB5cTqRCsZQYSMLJDkYRyQvDEBeRN88rEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, START_DATE = _t, COMPLETE_DATE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"START_DATE", type date}, {"COMPLETE_DATE", type date}}),
    // compare with start and complete date from drilled down values
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [START_DATE] >= CompareDate_Start and [COMPLETE_DATE] <= CompareDate_COMPLETE )
in
    #"Filtered Rows"

 

Hi EaglesTony ,

adudani  Good reply!
And you can also try this M function to add a custom column in the table ActiveTable:

if [START_DATE] >= List.Max(CompareDate[START DATE]) and [COMPLETE_DATE] <= List.Max(CompareDate[CALCENDATE]) then 1 else 0

Then you only need to filter the data for which the custom column = 1:

 

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.