Count the initial assignment date and initial reply of incident, the second, third, etc.

Hi, so I've consider as "Key Users" Salmu Lahmarmech (ID: E580333) and Chorouk Aboumejd (ID: E603167).

 

Just for sure: for Incident No 17499150 we calculate time between 1st row and last row in red rectangle. Correct?

 

Check this query. In [Detail] column you can find table with details. Let me know if this fits expected result:

 

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\Address\WEEKLY_DATA.xlsx"), true, true),
    #"Rapport 1_Sheet" = Source{[Item="Rapport 1",Kind="Sheet"]}[Data],
    FilteredRowsN2N2 = Table.SelectRows(#"Rapport 1_Sheet", each (Text.Trim([Nom de l Action Journal]) = "DE N2 VERS N2")),
    RenamedColumns = Table.RenameColumns(FilteredRowsN2N2,{{"Numéro de dossier", "Incident No"}, {"Date de l'action journal", "Date of Action"}, {"Nom de l assisteur de l action", "Assistant Name"}, {"ID assisteur de l'action", "Assistant ID"}, {"ID Recepteur", "Receiver ID"}, {"Détail journal dans la langue de la plateforme", "Receiver Name"}}),
    ChangedType = Table.TransformColumnTypes(RenamedColumns,{{"Incident No", Int64.Type}, {"Date of Action", type datetime}, {"Assistant ID", type text}, {"Assistant Name", type text}, {"Receiver ID", type text}, {"Receiver Name", type text}}),
    RemovedOtherColumns = Table.SelectColumns(ChangedType,{"Incident No", "Date of Action", "Assistant ID", "Assistant Name", "Receiver ID", "Receiver Name"}),
    GroupedRows = Table.Group(RemovedOtherColumns, {"Incident No"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table}, {"Detail", each 
        [ keyUsersId = List.Buffer({"E580333", "E603167"}),
          a = Table.AddIndexColumn(Table.Distinct(_, {"Date of Action", "Assistant ID"}), "Index", 1, 1, Int64.Type),
          b = Table.FillUp(Table.AddColumn(a, "Check", (x)=> if List.Contains(keyUsersId, x[Receiver ID]) then x[Index] else null), {"Check"}),
          c = Table.SelectRows(b, (x)=> x[Check] <> null),
          d = Table.Group(c, {"Check"}, {{"Dif", (x)=> if Table.RowCount(x) <= 1 then null else List.Max(x[Date of Action]) - List.Min(x[Date of Action]), type duration}}),
          e = Table.SelectRows(d, (x)=> x[Dif] <> null)[Dif]
        ][e], type table} }),
    Ad_FinalTable = Table.AddColumn(GroupedRows, "Final Table", each 
        [ a = Table.FromColumns({{[Incident No]}, [Detail], {[All]}}, type table[Incident No=Int64.Type, Duration=duration, Detail=table]),
          b = Table.FillDown(Table.AddIndexColumn(a, "Occurence", 1, 1, Int64.Type), {"Incident No", "Detail"})
        ][b], type table),
    CombinedFinalTable = Table.Combine(Ad_FinalTable[Final Table]),
    Ad_TotalDays = Table.AddColumn(CombinedFinalTable, "Total Days", each Number.Round(Duration.TotalDays([Duration]), 2), type number),
    Ad_TotalHours = Table.AddColumn(Ad_TotalDays, "Total Hours", each Number.Round(Duration.TotalHours([Duration]), 1), type number)
in
    Ad_TotalHours