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

[EaglesTony]

Hi,

  I have a table ActiveTable with rows:

ID  START_DATE   COMPLETE_DATE

1     01/31/2014   02/13/2024

2     02/14/2014   02/28/2024

3     02/14/2014   02/28/2024

4     02/14/2014   02/28/2024

   I have a 2nd table(called CompareDate with just 1 row):

START DATE  CALCENDATE

01/31/2014   02/16/2014

What I want is on the first table (ActiveTable) to filter down to ID's 1

So basically the logic is :

ACTIVETABLE_START_DATE >= COMPAREDATE_START DATE AND

ACTIVETABLE_START_DATE <= COMPAREDATE_CALCENDATE

[adudani · Accepted solution]

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"

[Anonymous · Accepted solution]

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.

[adudani · Accepted solution]

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"