[PowerBI] Create phases from one rows start date to another rows startdate

Dutsj

New Member
Joined
Jan 10, 2022
Messages
14
Office Version
  1. 365
Platform
  1. Windows
So i'm stuck on this issue at the moment. Basically i want to structure data into phases, because the descriptions end dates doesn't fully represent when the phase officially ends, the phase ends when the next phase begins.
I've tried to illustrate it here with some test data, left is how it looks, right is how i want it to look. I'm not sure yet because it's a huge dataset, but i'm pretty sure at some stages there might be missing rows and lack of data, in that case i just want it to be filled with "undefined" or similar.

I hope you can help me, it's an sql request in Power Query that i'm working with, and i've tried rearranging the data, this is the closest i could get to what i want.

Note: Phase 6 (last phase) doesn't have to be the description end date, i just typed it in there for now to make the data look better, that's not the part i'm stuck on.
1644930617741.png

Example data:
NameIDDate StartDate EndDescription
Name 1ID1
01-01-2021​
31-01-2021​
Phase 1 TaskName
Name 1ID1
01-02-2021​
31-01-2021​
Phase 2 TaskName
Name 1ID1
01-03-2021​
31-01-2021​
Phase 3 TaskName
Name 1ID1
01-04-2021​
31-01-2021​
Phase 4 TaskName
Name 1ID1
01-05-2021​
31-01-2021​
Phase 5 TaskName
Name 1ID1
01-06-2021​
30-06-2021​
Phase 6 TaskName
Name 2ID2
30-01-2021​
31-01-2021​
Phase 1 TaskName
Name 2ID2
30-05-2021​
31-01-2021​
Phase 5 TaskName
Name 2ID2
30-03-2021​
31-01-2021​
Phase 3 TaskName
Name 2ID2
30-04-2021​
31-01-2021​
Phase 4 TaskName
Name 2ID2
28-02-2021​
31-01-2021​
Phase 2 TaskName
Name 2ID2
01-06-2021​
30-06-2021​
Phase 6 TaskName
Name 3ID3
01-01-2022​
31-01-2021​
Phase 1 TaskName
Name 3ID3
05-05-2022​
31-01-2021​
Phase 5 Taskname
Name 3ID3
01-06-2022​
20-06-2021​
Phase 6 Taskname

Desired result:
NameIDPhasePhase startPhase endDescription date startDescription date endDescription
Name 1ID1Phase 1
01-01-2021​
01-02-2021​
01-01-2021​
31-01-2021​
Phase 1 TaskName
Name 1ID1Phase 2
01-02-2021​
01-03-2021​
01-02-2021​
31-01-2021​
Phase 2 TaskName
Name 1ID1Phase 3
01-03-2021​
01-04-2021​
01-03-2021​
31-01-2021​
Phase 3 TaskName
Name 1ID1Phase 4
01-04-2021​
01-05-2021​
01-04-2021​
31-01-2021​
Phase 4 TaskName
Name 1ID1Phase 5
01-05-2021​
01-06-2021​
01-05-2021​
31-01-2021​
Phase 5 TaskName
Name 1ID1Phase 6
01-06-2021​
30-06-2021​
01-06-2021​
30-06-2021​
Phase 6 TaskName
Name 2ID2Phase 1
30-01-2021​
28-02-2021​
30-01-2021​
31-01-2021​
Phase 1 TaskName
Name 2ID2Phase 2
30-05-2021​
30-03-2021​
28-02-2021​
31-01-2021​
Phase 2 TaskName
Name 2ID2Phase 3
30-03-2021​
30-04-2021​
30-03-2021​
31-01-2021​
Phase 3 TaskName
Name 2ID2Phase 4
30-04-2021​
30-05-2021​
30-04-2021​
31-01-2021​
Phase 4 TaskName
Name 2ID2Phase 5
28-02-2021​
01-06-2021​
30-05-2021​
31-01-2021​
Phase 5 TaskName
Name 2ID2Phase 6
01-06-2021​
30-06-2021​
01-06-2021​
30-06-2021​
Phase 6 TaskName
Name 3ID3Phase 1
01-01-2022​
Undefined
01-01-2022​
31-01-2021​
Phase 1 TaskName
Name 3ID3Phase 5
05-05-2022​
01-06-2022​
05-05-2022​
31-01-2021​
Phase 5 Taskname
Name 3ID3Phase 6
01-06-2022​
20-06-2021​
01-06-2022​
20-06-2021​
Phase 6 Taskname
 

Attachments

  • 1644930523930.png
    1644930523930.png
    64.9 KB · Views: 11

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top