Add Conditional Tasks to Existing Schedules

[forti4040]

Hello All,

I’m trying to add conditional tasks into an existing schedule within Power BI and I’m not sure how to setup applied steps vs. doing all of this manually every time.

I have an existing database where I’m pulling schedules.

They contain Project ID’s, Tasks within a project, and Start/Finish dates for each task.

Project ID   ,   Task     ,   Start    ,   Finish

ABC123      ,   Task 1  ,  3/1/19   ,  4/1/19

ABC123      ,   Task 2  ,  3/15/19  ,  4/19/19

XYZ567      ,   Task 1  ,  3/7/19   ,  4/21/19

XYZ567      ,   Task 2  ,  3/18/19  ,  4/5/19

Etc…

I then have another excel file I’m referencing that has additional tasks that I want to track in relation to each of the schedules. The additional tasks are not specific to a Project ID, but rather would apply to any project that I filter by.

Example

Task     ,          Start             ,              Finish

Task A  , Task 1 + 5 Days    ,   Task A Start + 5 days

Task B  , Task 2 + 15 Days  ,   Task B Start + 7 days

Etc…

This excel file has the following columns (but if needed I can change how this is laid out)

Task Name  ,  Duration  ,  Task to Reference

Task A          ,        5          ,          Task 1

Task B          ,       15         ,          Task 2

Does anyone know how I would be able to merge/append/combine these two sources so that I end up with a single schedule to reference for each project?

[v-lid-msft]

Hi @forti4040 ,

We can use the following steps to meet your requirement:

1. In the Power Query Editor, we can merge two tables based on the [Task to Reference] and then we can expand the column Project ID, Start and finish

2. Then we can create new calculated columns [start] and [end]. 

start = Merge1[Table.Start]+[Duration]

finish = [start]+[Duration]

Could you please share the logic why Task 2  add 7 days in finish while it has 15 duration?

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

[forti4040]

@v-lid-msft Thank you for the quick reply,

So far when I merge the tables with the settings you've shown and then expand the few columns I get null values for everything. I'm wondering if the fact that there other levels to the heirarchy is affecting the results. 

For example: 

Task 1 exists in North America, LATAM, and EMEA for the same project number. So within Project ABC123 there are multiple Start/Finish dates for Task 1 depending on the region. Does that impact how we would approach this?

The way I've added tasks manually in the past was to unpivot my task column, then add a new column with the specific task, populate with a formula referencing the other applicable task, then once again pivoting the data into a task column inclusive of my newly added column. This works...but for 150 columns it's a real pain... 

[v-lid-msft]

Hi @forti4040 ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[forti4040]

@v-lid-msft ,

I apologize for the delayed response. With all of the COVID-19 issues this task has temporarily taken a backseat at work. I truly appreciate your assistance and once I'm able to get back to this topic I will post a response on how your solution worked. 

[v-lid-msft]

Hi @forti4040 ,

We can try to create two calculated columns to meet your requirement instead of merge in power query editor.

start_column = [Start]+CALCULATE(SUM('Table (2)'[Duration]),'Table (2)'[Task to Reference] = EARLIER('Table'[Task]))

finish_column = [start_column]+CALCULATE(SUM('Table (2)'[Duration]),'Table (2)'[Task to Reference] = EARLIER('Table'[Task]))

Then we can get the result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

BTW, pbix as attched.

Best regards,