Hi Mynda,
Sorry for all the confusion.
Basically I am trying to calculate the average minutes within an hour of a day to process (load trucks) tickets.
Here is the formula I would like to achieve:
= Find "TicketTime-Hour", Average minutes within related hour in column "TicketTime"
see attached file.
Thanks
John
Hi John,
Thanks for further clarifying, however I'd like to make sure I'm understanding correctly. You said:
"How can I report the average time it took to process tickets within the range of each hour.
We are loading trucks and some hours are busier than others which is why I am trying to capture the number of minutes used within the hour of the day."
But your data only has a 'TicketTime' column, which I presume is the time the truck loading began, as opposed to the duration it took to load the truck.
I would have thought that you need the start and finish times for each truck load in order to calculate the 'average time it took to process tickets'? I just don't think your data will give you the information you're after.
You might be better off plotting a box and whisker chart that shows the first and last ticket times and the ticket count to give a perspective of the time range the trucks are being loaded in and the number of trucks in that time range.
Mynda
Hi Mynda,
I attached a file that contains more frequency of ticket time (loading times) to demonstrate the data I'm trying to capture.
Hopefully this will help clarify my challenge.
Thanks again for your help.
John
Hi John,
I'd use Power Query to calculate the 'Minutes from Last Ticket' and then you can use the PivotTable to average. See Sheet2 of file attached.
Mynda
Thanks Mynda,
I appreciate all your help.
I signed up for your Power Query course to help me better understand the application.
John
Hi Mynda,
Sorry another question, how do import more data so that it matches the current file layout?
Hi John,
Thanks for joining my Power Query course. I'm sure you'll get a lot out of it.
You can paste data to row 99 on Sheet 3 and it will be included in that table. You can then refresh the query and it will get the new data and run it through the Applied Steps. Then refresh the PivotTable.
However, if your data is coming from another file, then you're probably best to get the data from that file, that way you're not storing it in the query file three times; once in Sheet 2, once in Sheet3 and once in the Pivot Cache.
Mynda
Mynda Treacy said
Hi John,I'd use Power Query to calculate the 'Minutes from Last Ticket' and then you can use the PivotTable to average. See Sheet2 of file attached.
Mynda
I'm trying to replicate the Power Query formula since I am now sourcing from a share driver folder.
My problem is that I can't get past this part to determine the proper "Next TicketTime" and "Minutes from Last Time".
Can you please review and recommend?
let
Source = Folder.Files("S:Mississauga Reports - LogisticsGoderich Truck FlowTicket Review"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Ticket Review", each #"Transform File from Ticket Review"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Ticket Review"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Ticket Review", Table.ColumnNames(#"Transform File from Ticket Review"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"TicketDate", type datetime}, {"LocationID", Int64.Type}, {"DeliveryAddress1", type text}, {"TicketNo", Int64.Type}, {"SortName", type text}, {"TicketTime", type datetime}, {"OrderID", Int64.Type}, {"CarrierName", type text}, {"DeliveryAddress2", type text}, {"Description", type text}, {"Description2", type text}, {"CarrierID", Int64.Type}, {"ProductID", Int64.Type}, {"ProductName", type text}, {"CustomerID", type text}, {"VehicleID", type text}, {"VehicleName", Int64.Type}, {"Qty", type number}, {"Unit", type text}, {"TotalPrice", Int64.Type}, {"DispatchNo", Int64.Type}, {"DispatchDate", type datetime}, {"DispatchLoadID", Int64.Type}, {"VoidStatus", type text}, {"MaterialAmount", Int64.Type}, {"FreightAmount", Int64.Type}, {"OtherAmount", Int64.Type}, {"TaxAmount", Int64.Type}, {"FeeAmount", Int64.Type}, {"FreightTimeAmount", Int64.Type}, {"OtherFreightAmount", Int64.Type}, {"ZoneID", type text}, {"InvoiceNo", Int64.Type}, {"PurchaseOrder", type text}, {"UnitPrice", Int64.Type}, {"FreightRate", Int64.Type}, {"FreightPay", Int64.Type}, {"FreightPayAmount", Int64.Type}, {"Name", type text}, {"DestinationID", type any}, {"DestinationDescription", type any}, {"PreviousUniqueID", Int64.Type}, {"NextUniqueID", Int64.Type}, {"Credited", type text}, {"PRODUCT_DESCRIPTION", type text}, {"CityID", type text}, {"TicketID", type any}, {"UfText1", type any}, {"ReferTicketNo", Int64.Type}, {"TodayLoads", Int64.Type}, {"TodayQty", type number}, {"Ticket", Int64.Type}, {"Location", Int64.Type}, {"Date", type datetime}, {"Time", type datetime}, {"Customer Name", type text}, {"Order", Int64.Type}, {"Product Name", type text}, {"Carrier Name", type text}, {"Vehicle", type text}, {"Total Price", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Top Rows",{{"TicketDate", type date}, {"TicketTime", type time}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"LocationID", "DeliveryAddress1", "TicketNo", "SortName", "OrderID", "CarrierName", "DeliveryAddress2", "Description", "Description2", "CarrierID", "ProductID", "ProductName", "CustomerID", "VehicleID", "VehicleName", "Unit", "TotalPrice", "DispatchNo", "DispatchDate", "DispatchLoadID", "VoidStatus", "MaterialAmount", "FreightAmount", "OtherAmount", "TaxAmount", "FeeAmount", "FreightTimeAmount", "OtherFreightAmount", "ZoneID", "InvoiceNo", "PurchaseOrder", "UnitPrice", "FreightRate", "FreightPay", "FreightPayAmount", "Name", "DestinationID", "DestinationDescription", "PreviousUniqueID", "NextUniqueID", "Credited", "PRODUCT_DESCRIPTION", "CityID", "TicketID", "UfText1", "ReferTicketNo", "TodayLoads", "TodayQty", "Ticket", "Location", "Date", "Customer Name", "Order", "Product Name", "Carrier Name", "Vehicle", "Total Price"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Time", type time}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Time", "Next TicketTime"}, {"TicketTime", "Time"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Time", Order.Ascending}})
in
#"Sorted Rows"
Please see attached images. 
Hi John,
You just need to add a column that subtracts the TicketTime from the NextTicketTime.
Mynda
Hi Mynda,
I added the column but I'm coming up with the proper outcome.
I've attached a screen shot for you to see as well as the following editor.
let
Source = Excel.CurrentWorkbook(){[Name="Sheet1_1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"TicketDate", type date}, {"TicketTime", type time}, {"OrderID", Int64.Type}, {"Qty", type number}, {"TicketTime - Hour", type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"TicketTime - Hour"}),
#"Inserted Hour" = Table.AddColumn(#"Removed Columns", "Hour", each Time.Hour([TicketTime]), Int64.Type),
#"Added Index" = Table.AddIndexColumn(#"Inserted Hour", "Index", 0, 1),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index.1"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"TicketTime"}, {"Added Index1.TicketTime"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Added Index1",{{"Added Index1.TicketTime", "Next TicketTime"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Index", "Index.1"}),
#"Inserted Time Subtraction" = Table.AddColumn(#"Removed Columns1", "Subtraction", each [Next TicketTime] - [TicketTime], type duration),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Time Subtraction",{{"Subtraction", "Minutes from Last Ticket"}})
in
#"Renamed Columns1"
Thanks again 🙂
Ah, now that you've provided a bigger screenshot I can see that the values in the TicketTime and NextTicketTime columns are the same, so you'll need to revisit the step that brings in the NextTicketTime.
I don't see in your query where you've added the two index columns and then merged the query with itself to generate the 'NextTicketTime' column as per my query. The NextTicketTime column appears to have come from your source data. That's not how I created it.
Mynda
Sorry Mynda,
I can't seem to get it to work out.
Here is my latest attempt. I'm also missing the part of using "Int64" and I'm not sure how to include it to get the proper Next TicketTime and then the column Minutes from Last Ticket.
let
Source = Folder.Files("S:Mississauga Reports - LogisticsGoderich Truck FlowTicket Review"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Ticket Review", each #"Transform File from Ticket Review"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Ticket Review"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Ticket Review", Table.ColumnNames(#"Transform File from Ticket Review"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"TicketDate", type datetime}, {"LocationID", Int64.Type}, {"DeliveryAddress1", type text}, {"TicketNo", Int64.Type}, {"SortName", type text}, {"TicketTime", type datetime}, {"OrderID", Int64.Type}, {"CarrierName", type text}, {"DeliveryAddress2", type text}, {"Description", type text}, {"Description2", type text}, {"CarrierID", Int64.Type}, {"ProductID", Int64.Type}, {"ProductName", type text}, {"CustomerID", type text}, {"VehicleID", type text}, {"VehicleName", Int64.Type}, {"Qty", type number}, {"Unit", type text}, {"TotalPrice", Int64.Type}, {"DispatchNo", Int64.Type}, {"DispatchDate", type datetime}, {"DispatchLoadID", Int64.Type}, {"VoidStatus", type text}, {"MaterialAmount", Int64.Type}, {"FreightAmount", Int64.Type}, {"OtherAmount", Int64.Type}, {"TaxAmount", Int64.Type}, {"FeeAmount", Int64.Type}, {"FreightTimeAmount", Int64.Type}, {"OtherFreightAmount", Int64.Type}, {"ZoneID", type text}, {"InvoiceNo", Int64.Type}, {"PurchaseOrder", type text}, {"UnitPrice", Int64.Type}, {"FreightRate", Int64.Type}, {"FreightPay", Int64.Type}, {"FreightPayAmount", Int64.Type}, {"Name", type text}, {"DestinationID", type any}, {"DestinationDescription", type any}, {"PreviousUniqueID", Int64.Type}, {"NextUniqueID", Int64.Type}, {"Credited", type text}, {"PRODUCT_DESCRIPTION", type text}, {"CityID", type text}, {"TicketID", type any}, {"UfText1", type any}, {"ReferTicketNo", Int64.Type}, {"TodayLoads", Int64.Type}, {"TodayQty", type number}, {"Ticket", Int64.Type}, {"Location", Int64.Type}, {"Date", type datetime}, {"Time", type datetime}, {"Customer Name", type text}, {"Order", Int64.Type}, {"Product Name", type text}, {"Carrier Name", type text}, {"Vehicle", type text}, {"Total Price", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Top Rows",{{"TicketDate", type date}, {"TicketTime", type time}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"LocationID", "DeliveryAddress1", "TicketNo", "SortName", "OrderID", "CarrierName", "DeliveryAddress2", "Description", "Description2", "CarrierID", "ProductID", "ProductName", "CustomerID", "VehicleID", "VehicleName", "Unit", "TotalPrice", "DispatchNo", "DispatchDate", "DispatchLoadID", "VoidStatus", "MaterialAmount", "FreightAmount", "OtherAmount", "TaxAmount", "FeeAmount", "FreightTimeAmount", "OtherFreightAmount", "ZoneID", "InvoiceNo", "PurchaseOrder", "UnitPrice", "FreightRate", "FreightPay", "FreightPayAmount", "Name", "DestinationID", "DestinationDescription", "PreviousUniqueID", "NextUniqueID", "Credited", "PRODUCT_DESCRIPTION", "CityID", "TicketID", "UfText1", "ReferTicketNo", "TodayLoads", "TodayQty", "Ticket", "Location", "Date", "Customer Name", "Order", "Product Name", "Carrier Name", "Vehicle", "Total Price", "Time"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns1", "Index", 0, 1),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
#"Added Custom" = Table.AddColumn(#"Added Index1", "Hour", each [TicketTime]),
#"Inserted Minute" = Table.AddColumn(#"Added Custom", "Minute", each Time.Minute([Hour]), Int64.Type)
in
#"Inserted Minute"
Hi John,
You haven't done all of the steps from the first 'Added Index' step. You've skipped the Merged Queries step etc.
Mynda
