Logistics Dashboard Need Guidance on Defining Shipment Stops

[kaym]

Hello, 

Fellow analysts, I am dealing with a little messy logistics data currently and after lots of cleaning, I have been stuck at one place for a while now. I am trying to do my best to explain it in easy terms:

there are shipments (shipment#) with multiple stops, including pickups and deliveries. Each stop has a type (pickup or delivery) and an estimated delivery date is mentioned for rows with stop type = delivery.

I need to address the following issues:

1. Determine the correct order of stops within each shipment.
2. Assume that pickups should always be followed by delivery stops.
3. Ensure that the truck delivers first to the location where the estimated delivery date is earliest.

There is one example that is giving me a headache - 
There are pickups from 2 different locations and delivery at 2 different locations:

Shipment #	Ref #	Stop Type	Location	Estimated Delivery Date
12345	A	Pickup	Warehouse A, Texas	-
12345	A	Delivery	Customer C, New York	2023-01-12
12345	B	Pickup	Warehouse B, Florida	-
12345	B	Delivery	Customer D, Maine	2023-01-15

So, the correct order or route for this shipment (12345) should be :  Texas - Florida - New York - Maine

What can I do in Power Query to ensure stops are labelled for each shipment? 

Thank you!

[jgeddes]

You may want to look at table grouping and nested table sorting to get the result you are looking for. Here is an example code you can paste into the advanced editor of a blank query. It may not be the exact solution you need to should help point you in the right direction.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lFyBOKAzOTs0gIgIzyxKDUjv7Q4VcFRRyEktSKxGCioFKuDUO6EXbmTjoJbTn5RZkoiugaQ+S6pOZllqUWVQKZzaXFJfm5qkYKzjoJfarlCZH5RNlDYwFDf0EjfyMDIGMM2bJpddBR8EzPzUqE6TaE6YwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Shipment = _t, Ref = _t, #"Stop Type" = _t, Location = _t, ETA = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Shipment", Int64.Type}, {"Ref", type text}, {"Stop Type", type text}, {"Location", type text}, {"ETA", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Shipment", "Ref"}, {{"_nestedTable", each _, type table [Shipment=nullable number, Ref=nullable text, Stop Type=nullable text, Location=nullable text, ETA=nullable date]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"_nestedTable", each Table.Sort(_, {{"Stop Type", Order.Descending}, {"ETA", Order.Ascending}})}}),
    #"Expanded _nestedTable" = Table.ExpandTableColumn(Custom1, "_nestedTable", {"Stop Type", "Location", "ETA"}, {"Stop Type", "Location", "ETA"})
in
    #"Expanded _nestedTable"