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
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])
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
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!