Hi,
I am new to Power BI and I am struggling with a task I am unable to figure out how to implement. I have two tables, both of which has an ID-field with the same type of ID. The first table has a date-column of when a transaction happened, and looks somewhat like this:
Table 1:
| ID | Date (DD.MM.YYYY) | Other relevant stuff |
| 1 | 1.1.2020 | ... |
| 1 | 1.2.2020 | ... |
| 2 | 1.2.2020 | ... |
| 2 | 1.3.2020 | ... |
| 2 | 1.4.2020 | ... |
| 3 | 1.1.2020 | ... |
| 3 | 1.2.2020 | ... |
The second table has the ID-column, a columnd with a date marking the start of an interval, a column with a date marking the end of the interval, and an additional column with a product description:
Table 2:
| ID | Start date | End date | Product |
| 1 | 1.1.2020 | 1.2.2020 | Bicycle |
| 2 | 1.1.2020 | 1.2.2020 | Bicycle |
| 2 | 1.3.2020 | 1.5.2020 | Cellphone |
| 3 | 1.1.2020 | 1.2.2020 | Chair |
I would want the "Product"-column to be added to the first table, based on the ID and that the Date is between the Start date and End date. So I want the resulting table to be
| ID | Date (DD.MM.YYYY) | Other relevant stuff | Product |
| 1 | 1.1.2020 | ... | Bicycle |
| 1 | 1.2.2020 | ... | Bicycle |
| 2 | 1.2.2020 | ... | Bicycle |
| 2 | 1.3.2020 | ... | Cellphone |
| 2 | 1.4.2020 | ... | Cellphone |
| 3 | 1.1.2020 | ... | Chair |
| 3 | 1.2.2020 | ... | Chair |
So for ID=2, we see that the product at Date=1.2.2020 should be "Bicycle", as Table1[ID] = Table2[ID] and Table1[Date] is between Table2[Start date] and Table2[End date].
Also note that both datasets are quite large (1.6 million rows in Table1 and 270k rows in Table2). Hoping someone are able to help!
