Date between Dates and Status per day

[T-Pan]

I have the following table that illustrates all the management stages that a ticket goes through in the company.
The CREATED DATE column is common to all rows by ticket number as it indicates the moment the ticket is created in our system.
The RESEARCHING DATE column indicates when someone starts managing the ticket.
The MOVE TO OTHER DEPARTMENT DATE column indicates the moment when the department needs to go for examination in another department.
The MOVE TO FINAL CHECH column indicates the moment the ticket goes to the department that gives the final approval.
The RESOLVED DATE column states when the ticket was resolved.
Column RESEARCHING DATE will always have a value as in every other stage where the ticket is, it will always return to this stage to go again if needed to another stage.
I want to generate the days between these dates and state the STATUS (HEADERS OF COLUMNS) it is in if date.

Thanks in advance.

[dufoq3]

Hi, what should be result for this sample data?

[T-Pan]

[dufoq3 · Accepted solution]

Hi @T-Pan, this query evaluates each ID separately.

Result:

let
    fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table =>
        //v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ
        let
            a = Table.Column(tbl, col),
            b = if shift = 0 or shift = null then a else if shift > 0
                then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
                else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),    
            c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
                ( if newColName <> null then {newColName} else
                    if shift = 0 then {col & "_Duplicate"} else
                    if shift > 0 then {col & "_PrevValue"} 
                    else              {col & "_NextValue"} )),
            d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any})
        in
            d,

    fnTransform = 
        (myTable as table)=>
        [
            // _Detail = GroupedRows{[ID="1"]}[All],
            _Detail = myTable,
            _AddedIndex = Table.AddIndexColumn(_Detail, "Index", 0, 1, Int64.Type),
            _UnpivotedOtherColumns = Table.UnpivotOtherColumns(_AddedIndex, {"ID", "Index"}, "STATUS", "Value"),
            _ChangedType = Table.TransformColumnTypes(_UnpivotedOtherColumns,{{"Value", type datetime}}, "en-US"),
            _Ad_FilterHelper = Table.AddColumn(_ChangedType, "FilterHelper", each if [Index] = 0 then 1 else if List.Contains({"CREATED DATE", "RESEARCHING DATE"}, [STATUS]) then 0 else 1, Int64.Type),
            _FilteredRows = Table.SelectRows(_Ad_FilterHelper, each ([FilterHelper] = 1) and ([Value] <> null)),
            #"_Sorted Rows" = Table.Sort(_FilteredRows,{{"Value", Order.Ascending}}),
            _ShiftedValue = fnShift(#"_Sorted Rows", "Value", -1, null, type datetime),
            _Ad_Dates = Table.AddColumn(_ShiftedValue, "Dates", each 
                [ a = Date.From([Value]),
                  b = Date.From([Value_NextValue]),
                  c = try List.Dates(a, Duration.TotalDays(b-a), #duration(1,0,0,0)) otherwise {a}
                ][c], type list ),
            _RemovedOtherColumns = Table.SelectColumns(_Ad_Dates,{"ID", "Dates", "STATUS"}),
            _ExpandedDates = Table.ExpandListColumn(_RemovedOtherColumns, "Dates"),
            _ChangedType2 = Table.TransformColumnTypes(_ExpandedDates,{{"Dates", type date}, {"ID", Int64.Type}}),
            _ReplacedValue = Table.ReplaceValue(_ChangedType2," DATE","",Replacer.ReplaceText,{"STATUS"})
        ][_ReplacedValue],

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MjAyUTA0sDI2UHD0BYuZ6oNEjKwMDRQCICJmYBFDKxOoGhCK1cFnhrk+RMQIoQMhCFNGwAxDA6h6A1OEtUgSQBeaglwYGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"CREATED DATE" = _t, #"RESEARCHING DATE" = _t, #"MOVE TO OTHER DEPARTMENT DATE" = _t, #"MOVE TO FINAL CHECK" = _t, #"RESOLVED DATE" = _t]),
    GroupedRows = Table.Group(Source, {"ID"}, {{"Transformed", fnTransform, type table}}),
    CombinedTransformed = Table.Combine(GroupedRows[Transformed])
in
    CombinedTransformed