Power Query select rows between two dates

[Anonymous]

Can you please help me, i have two tables one looks like this

Eqmt     Date
A       15/08/20
B        03/09/20
C        05/08/20

And another that looks like:

Eqmt Dev    IniDate      EndDate

A       1      01/jul/20    01/aug/20

A       3      01/aug/20  01/sep/20

B       4      01/jul/20    01/aug/20      

B       5      01/aug/20  01/sep/20

B       2      01/sep/20  01/oct/20

C       4      01/jul/20    01/aug/20

C       3      01/aug/20  01/sep/20

I want to capture the Dev value that is between IniDate and EndDate, something like this:

Eqmt     Date      Dev
A       15/08/20   3
B        03/09/20  2
C        05/08/20  3

Can you provide me some answer, withouth using DAX functions if apossible? Thanks for your answer

[Watsky]

Hey @Anonymous . Assuming you're smallest table is called Table1 and the larger is called Table2 what I did was:

1. Merged Table2 into Table 1 only having Dev, IniDate, and EndDate added.

2. Added a custom column using the following if statement:

if [Date] < [EndDate] and [Date] > [IniDate] then true else false

3. Filtered to show only true

4. Removed the IniDate, EndDate and Custom Columns leaving only the Date, Eqmt, and Dev.

Not the most graceful but it gets the job done in Power Query.  This is what the M looks like for it. Granted you wouldn't want to use my source you'd want to use your own.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA3NNU3MjAyUNJRclSK1YlWstQ3hgk4gQUs9OEqnJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Eqmt = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Eqmt"}, Table2, {"Eqmt"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Dev", "IniDate", "EndDate"}, {"Dev", "IniDate", "EndDate"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table2", "Custom", each if [Date] < [EndDate] and [Date] > [IniDate] then true else false),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "IniDate", "EndDate"})
in
    #"Removed Columns"

Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up! ?
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

[HotChilli · Accepted solution]

I think this does it.  Please test.

Add a column in the first table (swap your second table name for TableB

List.First(
            Table.SelectRows(
                TableB,
                (r) =>
                    r[IniDate] < [Date]
                    and
                    r[EndDate] > [Date]
                    and
                    r[Eqmt] = [Eqmt] 
            )[Dev]
        )

[ArchanaNayak]

Thank you so much
This was really helpful for me

[Anonymous]

If we remove the list.first part then also I think this would work fine. Has anyone tried this? If TableB has many columns to be matched with the provided conditions then we can use the loop without the list.part fuunction.

[IWallis]

I have a similar question to this. If you can provide some solutions, it would be great!

I would like to create a date selector. I have two columns, 1)start_date, 2)end_date, what I need is I want to click on the date selector (pick a particular date) and if the date falls in between the start_date and end_date, I need the rows' information to show in a table. Can I use the similar query to do this? Or a what if parameter will work? Or the loop like you mentioned will work? But I am not sure how to use loop....

thank you very much!

[HotChilli]

This is a data-shaping question in Power Query. 

I think that you want a date slicer using the 'between' option.  So, different requirements.

It's probably better to always raise a new post for any issues - posting on a solved thread won't get many views.  Anyway, try the slicer and see how you get on.

[IWallis]

Hi, Thanks. the slicer is not the solution. But I will post a new post to discuss this question. Thank you so much for replying!