Dashboards
Power Pivot
Power Query
June 7, 2019
July 16, 2010
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
Dashboards
Power Pivot
Power Query
June 7, 2019
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 - Logistics\Goderich Truck Flow\Ticket 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"
Dashboards
Power Pivot
Power Query
June 7, 2019
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 🙂
July 16, 2010
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
Dashboards
Power Pivot
Power Query
June 7, 2019
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 - Logistics\Goderich Truck Flow\Ticket 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"
1 Guest(s)