Expanding dates when you have got duplicate rows.

[sgrwntest]

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!

[Thejeswar]

@sgrwntest,

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

[sgrwntest · Accepted solution]

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

😎 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]

@sgrwntest,

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