Forum Discussion
Join three tables while using a between dates clause
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
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.