Forum Discussion
Expanding dates when you have got duplicate rows.
Hello all,
I have got this data set:
| Product | SaleStartEnd | Dates |
| A | Sale Start | 10/03/18 |
| A | Sale End | 27/03/18 |
| B | Sale Start | 20/03/18 |
| B | Sale End | 31/05/18 |
| B | Sale Start | 01/08/18 |
| B | Sale End | 31/08/18 |
| C | Sale Start | 01/06/18 |
| C | Sale End | 15/06/18 |
| D | Sale Start | 02/07/18 |
| D | Sale End | 28/09/18 |
I want to convert it into:
| Product | Sale Start | Sale End |
| A | 10/03/18 | 27/03/18 |
| B | 20/03/18 | 31/05/18 |
| B | 01/08/18 | 31/08/18 |
| C | 01/06/18 | 15/06/18 |
| D | 02/07/18 | 28/09/18 |
I am pretty confident I can do this in excel VBA. But how do we achieve this in Power BI/Power Query?
Thanks in advance for any assistance! Any questions please let me know!
I think I solved it myself, here is the solution:
1) Add index, starting from 1 and incrementing by 1
2) Change index to text
3) Join Product & Index
4) Remove Product column
5) Add another index, starting from 1 and incrementing by 1
6) Pivot Based on Dates & Don't Aggregate
7) Sort index in ascending created in step 5
8) For Start Dates Fill Down.
9) For End Dates Fill up.
10) Now create a new custom column that concatenates Product, Start date, End Date. This is to remove duplicates
11) Remove duplicates in the column in step 10.
Unsure if there is a more easier way....I will mark as solved after few days, incase anyone has better solution.
3 Replies
I think I solved it myself, here is the solution:
1) Add index, starting from 1 and incrementing by 1
2) Change index to text
3) Join Product & Index
4) Remove Product column
5) Add another index, starting from 1 and incrementing by 1
6) Pivot Based on Dates & Don't Aggregate
7) Sort index in ascending created in step 5
8) For Start Dates Fill Down.
9) For End Dates Fill up.
10) Now create a new custom column that concatenates Product, Start date, End Date. This is to remove duplicates
11) Remove duplicates in the column in step 10.
Unsure if there is a more easier way....I will mark as solved after few days, incase anyone has better solution.
- Thejeswar
Super User
The Below is a much simpler solution. In a way, optimization of your solution
1. Add index, starting from 1 and incrementing by 1
2. Pivot Based on Dates & Don't Aggregate
3. For Start Dates Fill Down.
4. For End Dates Fill up.
5. Remove Index Column
6. Remove Duplicates for the entire table
- Thejeswar
You can use Pivot Column option on Dates to achieve your second Table. But the limitation here is the Pivot Column will group the Data and hence requires some aggregation to be provided for Dates Column.
If you set it as Don't Aggregate, then if multiple entries are there, it will show an error
