Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you so much!