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