Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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!
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"
Proud to be a Super User! | |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.