cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sgrwntest
Regular Visitor

Expanding dates when you have got duplicate rows.

Hello all,

 

I have got this data set:

 

ProductSaleStartEndDates
ASale Start10/03/18
ASale End27/03/18
BSale Start20/03/18
BSale End31/05/18
BSale Start01/08/18
BSale End31/08/18
CSale Start01/06/18
CSale End15/06/18
DSale Start02/07/18
DSale End28/09/18

 

I want to convert it into:

 

ProductSale StartSale End
A10/03/1827/03/18
B20/03/1831/05/18
B01/08/1831/08/18
C01/06/1815/06/18
D02/07/1828/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!

1 ACCEPTED SOLUTION
sgrwntest
Regular Visitor

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.

View solution in original post

3 REPLIES 3
Thejeswar
Solution Sage
Solution Sage

@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
Regular Visitor

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.

View solution in original post

@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

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors