Forum Discussion

kaym's avatar
kaym
Icon for Helper I rankHelper I
2 years ago

Logistics Dashboard Need Guidance on Defining Shipment Stops

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!

Need Help

1 Reply

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    2 years ago

    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"