Forum Discussion
Count the initial assignment date and initial reply of incident, the second, third, etc.
Hello all, I'm struggling to find the right calculations in either Power query or Dax for the following problem: I have a table that contains the details of incidents treated by level 3 technic...
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
dufoq3
Community Champion
Community ChampionHi, 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
I went ahead and tested it further, now it works fine, just now trying to figure out how to display the right action date for each ticket, thanks a lot for your support, you rock! 👏😊
- dufoq3
Check this.
Result:
let Source = Excel.Workbook(File.Contents("C:\Users\a028311\OneDrive - Volvo Group\Downloads\PowerQueryForum\JackReacher\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"}, {{"Result", (x)=> if Table.RowCount(x) <= 1 then null else [Assign Date = List.Min(x[Date of Action]), Reply Date = List.Max(x[Date of Action]), Dif = #"Reply Date" - #"Assign Date"]}}), e = try Table.FromRecords(List.Select(d[Result], (x)=> x <> null)) otherwise null ][e], type table} }), Ad_FinalTable = Table.AddColumn(GroupedRows, "Final Table", each [ a = Table.FromColumns({{[Incident No]}} & (if [Detail] = null or Table.IsEmpty([Detail]) then Table.ToColumns(#table(null, {List.Repeat({null}, 3)})) else Table.ToColumns([Detail])) & {{[All]}}, type table[Incident No=Int64.Type, Assign Date=datetime, Reply Date=datetime, 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