Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Thank you so much!
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |