Forum Discussion
Help with date subtraction with some criteria from different rows
Hi Stanford ,
Thanks for reaching out to Microsoft Fabric Community Forum.I have reproduced your scenario using Power Query and confirmed the behavior you described. The issue arises when referencing another task’s date, such as "Debug Setup," within the same Job #, particularly if task order varies or certain tasks are absent. To address this, I matched task names within a grouped query (grouped by Job #) instead of using row index. In the grouped table, I created a custom column that retrieves the "Task Must Start Date" for "Debug Setup" and assigns it as the "Task Req’d Date" for "Ready for Parts".
The custom code applied in the grouped step is :
let
taskTable = [AllTasks],
debugDate = try Table.SelectRows(taskTable, each [Task Description] = "Debug Setup"){0}[Task Must Start Date] otherwise null,
addReqdCol = Table.AddColumn(taskTable, "Task Req'd Date", each if [Task Description] = "Ready for Parts" then debugDate else null)
in
addReqdColThis method functions for each job, and if "Debug Setup" is not present, the "Task Req’d Date" will remain null as intended.
As shown below, the Task Req’d Date is populated only when "Debug Setup" exists within the same Job #:Please let me know if you have any questions or need further assistance.
Regards,
Sreeteja.
Good morning,
Not every job will need every one of those tasks. For example some jobs may not need "Capital Assembly" since machine is being re-used. Tasks may get shifted around in the table layout for sure, but tasks will need completed in that order if the task is required. For example: "Prints" will have to be done before "Tooling Assembly" and "Prints" and "PLC Program" will have to be complete before "Ready for Parts" can happen. Hope that makes sense.
Ok, so we can't directly reference task names then. We can use an index to reference 'previous' task in the project, but will rely on the tasks being ingested/loaded from source in the correct order that they need to be done in (unless you already have a task index column or task order column elsewhere in the table?).
I may not get to look at this today now I'm afraid, sorry. If not tonight, it will be first thing tomorrow morning. However, the basic method I would implement would be as follows, if you wanted to have a go until I can put together a proper solution/example for you:
-1- Group table on [Job #] using the All Rows aggregated column.
-2- Add index column starting from 0 to the nested data table.
-3- Expand required columns back out of nested table.
-4- Use a custom column formula something like this:
// Assuming index column added called "TaskOrder"
Task Req'd Date = try PreviousStepName[Task Must Start Date]{[TaskOrder] - 1} otherwise null
Pete