Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
EaglesTony
Helper V
Helper V

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

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

 

2 ACCEPTED SOLUTIONS
adudani
Super User
Super User

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"

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

View solution in original post

v-junyant-msft
Community Support
Community Support

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

vjunyantmsft_0-1708480959795.png

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

vjunyantmsft_1-1708481015029.png
vjunyantmsft_2-1708481022077.png

 

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.

View solution in original post

2 REPLIES 2
v-junyant-msft
Community Support
Community Support

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

vjunyantmsft_0-1708480959795.png

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

vjunyantmsft_1-1708481015029.png
vjunyantmsft_2-1708481022077.png

 

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
Super User
Super User

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"

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors