Custom column that retrieves the Min date after "Suspend" date

[TariqArnabi]

I'm trying to retrieve the minimum date AFTER the tasks moves out of suspense 

so currently the columns are as follows (The Out of Suspend will be the custom column added)

What I'll like to retrieve via M language is to retrieve the next date after the a task went into Suspend 
example 
Task 1, went into suspend 08/15/2021 - So out of suspend date will be 09/30/2021
Task 2, went into suspend 01/10/2022 - So out of suspend date will be 02/7/2022
Task 3, went into suspend 10/26/2021 - So out of suspend willbe 11/22/2021

I utilized the following to create a custome column that gave me a differece in date, where i added the difference to retrieve the closest date.    Suspend is 10/26/2021 - Closes date is 11/15/2021 = 19:21:48:01  ..
Only issue i havent been able to convert that back to a date, or add it to the 10/26/2021 

IF [Suspend] < (List.Max({[Submitted,[#"Quote"],[Processed],[Proposed],[Waiting ]}))
THEN (List.Max({[Submitted],[#"Quote"],[Binding Processed],[Proposed],[Waiting]})) -
[Suspend] ELSE NULL

Is there a better more efficient way to do it? 

[Anonymous · Accepted solution]

Hi @TariqArnabi ,

I duplicated the original table as Table 2and used Unpivot -->Group--Add column(use Try..Otherwise to  handle errors) to get such output:

Then go back to the original table, "Merge Queries" to get the matched [Out of Suspend] column:

Below is the whole M syntax:

For Table 2:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY/BDcAgDAN3ybtSiENbmAWx/xo1glAkHjkbB9OamFySXgUUCQE1gKeqp4GYWNTu6faryRATpbLT0Dduu9pOYr2wAvAdME7TGQudktlRZzC0RJZzDovb6zHj+XtlSo+aR4xWOT7BigdJ7x8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TaskID = _t, Submitted = _t, Quote = _t, Processed = _t, Proposed = _t, Waiting = _t, Suspend = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TaskID", Int64.Type}, {"Submitted", type date}, {"Quote", type date}, {"Processed", type date}, {"Proposed", type date}, {"Waiting", type date}, {"Suspend", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"TaskID", "Suspend"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"TaskID", "Suspend"}, {{"All", each _, type table [TaskID=nullable number, Suspend=nullable date, Attribute=text, Value=date]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Out of Suspend", each try Table.Min( Table.SelectRows([All], each [Suspend]<[Value]),"Value" )[Value] otherwise null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"All", "Suspend"})
in
    #"Removed Columns"

For Original table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY/BDcAgDAN3ybtSiENbmAWx/xo1glAkHjkbB9OamFySXgUUCQE1gKeqp4GYWNTu6faryRATpbLT0Dduu9pOYr2wAvAdME7TGQudktlRZzC0RJZzDovb6zHj+XtlSo+aR4xWOT7BigdJ7x8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TaskID = _t, Submitted = _t, Quote = _t, Processed = _t, Proposed = _t, Waiting = _t, Suspend = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TaskID", Int64.Type}, {"Submitted", type date}, {"Quote", type date}, {"Processed", type date}, {"Proposed", type date}, {"Waiting", type date}, {"Suspend", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"TaskID"}, #"Table 2", {"TaskID"}, "Table 2", JoinKind.LeftOuter),
    #"Expanded Table 2" = Table.ExpandTableColumn(#"Merged Queries", "Table 2", {"Out of Suspend"}, {"Out of Suspend"})
in
    #"Expanded Table 2"

Refer to:

Error handling - Power Query | Microsoft Docs

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TariqArnabi]

So i was able to edit the query and i believe i got it coming in correctly, only issue is that if the suspend is null, it comes as an error

= IF [Suspend] <= (List.Max({[Submitted,[#"Quote"],[Processed],[Proposed],[Waiting ]}))
THEN (List.Max({[Submitted],[#"Quote"],[Binding Processed],[Proposed],[Waiting]})) -
[Suspend] ELSE [Suspend]

Changed the type to Whole Number  then added another custome column with a 
= Date.AddDays ([Suspend], [Custom])   

I believe that is currently working, going thru the columns to make sure everything is correct