Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to 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.
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
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.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
86 | |
85 | |
68 | |
67 | |
63 |
User | Count |
---|---|
210 | |
118 | |
116 | |
81 | |
74 |