Log In
Register
Home
November 27, 2018
Offline
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 |
See Answer
Trusted Members
October 18, 2018
Offline
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.
November 27, 2018
Offline
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
Active Member
Power Query
June 14, 2022
Offline
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.
Answers Post
November 27, 2018
Offline
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!!
1 Guest(s)
