Join three tables while using a between dates clause

[palmersong]

I have three excel tables that I would like to join together.  If this is SQL, it would look like this:
FROM employee e
INNER JOIN dates d

  ON d.DATE between e.START_DATE and e.END_DATE

LEFT JOIN timetracking t

  ON e.EMPLOYEE = t.EMPLOYEE

  AND d.DATE = t.DATE

I am struggling with how to do this in PowerBI / PowerQuery for the following reasons:

- When I try to join the datasources in PowerBI Model View, I can only join on 1 instance of field = field (not multiple, not between)

- In PowerQuery, when I merge the queries, I Full Outer Join all dates to the employee table, then create a column that says whether it is in the daterange.  But I can't do a Filter step on it.  I also can't join on a field from the base table AND one from the merged table.

How do I achieve a join like this, even if I need to bring in way more records than necessary and then set a filter in the report?

[Anonymous]

Hi @palmersong 
Thank you for reaching out microsoft fabric community forum.
I wanted to check if you had the opportunity to review the information provided by @PwerQueryKees . Please feel free to contact us if you have any further questions. If his response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[palmersong]

@rohit1991 - I merged the queries and created the column, but there is no ability to filter the column because it says Table and the filter pulldown is no longer a pulldown button. (See first image below)

@Jai-Rathinavel I have tried to use your code but I keep getting a "Token Eof Expected" error on the first comma.  Here is the code as I have it.  Not sure I translated your code to my data properly, so what I want to do is have Dates.Date to be BETWEEN Employee Table.VALID_FROM_DT and Employee Table.VALID_TO_DT.  

Thank you both for your help!

Source = "Employee Table",
#"Added Date Range" = Table.AddColumn(Source, "Employee Table", each List.Dates([VALID_FROM_DT], Duration.Days([VALID_TO_DT] - [VALID_FROM_DT]) + 1, #duration(1,0,0,0))),
#"Expanded Dates" = Table.ExpandListColumn(#"Added Date Range", "DateRange"),
#"Merged Dates" = Table.NestedJoin("Expanded Dates", [DateRange],"Dates",[Date])

[PwerQueryKees · Accepted solution]

Some points:

• Which comma do you consider the "first"? The one after "Source = "Employee Table",?
• And I think it should read: Source = #"Employee Table",
• The core of the solution of @Jai-Rathinavel (which I would use), is the AddColumn:
  =Table.AddColumn(Source, "DateRange", each List.Dates([START_DATE], Duration.Days([END_DATE] - [START_DATE]) + 1, #duration(1,0,0,0)))​
• So add a new column (anything will do) and replace the formula generated with the one above.
• Replace Source with the name of the step producing the (selection of) the Employee Table.
• This will create column containg a list (not a table) of all dates betweeen your to and from dates.
• Once you have that list, you expand it by clicking 
• Then merge you tracking table as required on the empploee id and the relevant date field
  
  

  Kees Stolker

  A big fan of Power Query and Excel

[palmersong]

Thank you - I had been doing all the formulas mentioned and merging but the thing I was unaware of was the step to expand the table so you can then join using those columns.  Thank you!

[rohit1991]

Hi @palmersong ,

You're correct that Power BI's data model and Power Query don't support SQL-style joins with range conditions like BETWEEN directly, but there is a workaround using Power Query. The approach you mentioned—performing a full outer join between the dates and employee tables—is on the right track. After the merge, you can create a custom column to evaluate whether d.DATE falls between e.START_DATE and e.END_DATE, using a condition like if [DATE] >= [START_DATE] and [DATE] <= [END_DATE] then true else false.

Once that column is created, you should filter the result within Power Query using the applied steps pane or a custom filter step to keep only the rows where this new column is true. If you're struggling to filter at that stage, make sure the data types match (i.e., both DATE, START_DATE, and END_DATE are all date types), as mismatches can silently cause logic failures. Once the filtered table is ready, you can then perform a standard LEFT JOIN to the timetracking table using both EMPLOYEE and DATE as join keys. This will effectively replicate your desired SQL logic. While this method might result in a large intermediate dataset, it ensures correctness and allows for flexible reporting later on.

[Jai-Rathinavel]

Hi @palmersong You can refer the below M code to generate the records only for the dates bewteen the Start and End Date from your primary table. Then Proceed with the merge query step

    Source = EmployeeTable,
    #"Added Date Range" = Table.AddColumn(Source, "DateRange", each List.Dates([START_DATE], Duration.Days([END_DATE] - [START_DATE]) + 1, #duration(1,0,0,0))),
    #"Expanded Dates" = Table.ExpandListColumn(#"Added Date Range", "DateRange"),
    #"Merged Dates" = Table.NestedJoin(#"Expanded Dates", YourSecondTable,.....)

Thanks,

Jai Rathinavel | LinkedIn