I have a set of codes each month, and a date on each code - 202201, 202202, 202203, etc. I can turn those codes into dates, for example '01/15/2022', '02/15/2022', '03/15/2022'
Each month the code has a number of rows and columns in a lookup table, defined by an effective and end date.
For example, Code1 has two date ranges: Effective from 01/01/2021 to 01/31/2022, and then another range from 02/01/2022 to a high end date (still active).
I cannot find an example of how to join two data sets based on a join field (the code) between two dates in my lookup table, where I return multiple columns and rows from the lookup table. I am trying to understand the products associated with my codes over time, and I know they change.
Are there functions or techniques to do this?
Any suggestions, pointers or concepts to research would be greatly appreciated! Thanks - Beth
| Month | Code | Code | Effective | End | Products | |
| 202201 | Code1 | Code1 | 1/1/2021 | 1/31/2022 | a | |
| 202203 | Code1 | Code1 | 1/1/2021 | 1/31/2022 | b | |
| 202203 | Code1 | Code1 | 1/1/2021 | 1/31/2022 | c | |
| Code1 | 2/1/2022 | 12/31/9999 | a | |||
| Code1 | 2/1/2022 | 12/31/9999 | b | |||
| Code1 | 2/1/2022 | 12/31/9999 | d | |||
| Code1 | 2/1/2022 | 12/31/9999 | e |
Trying to understand what you want the end result for the data in the two tables to look like. Can you please supply a mocked up version for the data you have supplied. If you upload sample data then we will not have to retype your source data and mocked up result to achieve a workable solution.
Sure. See attached. I also included typical SQL I would use if the data were in SQL Server... I meant to name my tables for you, but now can't seem to delete the first upload...
Appreciate any help Alan! Beth
I've had this problem before, and I eventually concluded that there is no such thing as "between" in Power Query.
There are multiple options, including adding a custom column that contains a list of all the dates possible, then joining on that date column, then filtering, but it's extremely expensive.
This solution (link below) seems to have a better way, and it's similar to a "between" join, but also requires a custom column. You might try this out first.
https://community.powerbi.com/t5/Desktop/Power-Query-only-Join-on-Range-of-Dates/m-p/505197
THANKS ADAM!
This worked on my test data, and lets me pull in any of the columns I need from the reference table. I have some fairly large data sets to work with, so I will have to see if this is efficient enough to be an actual solution, but for now - looks like it works like a charm! 
CHEERS!!
Including the solution file, with power queries. Beth
