Forum Discussion
Find flight itinerary from flight legs
A flight with destination from A to B is composed by one or more legs. It has 2 legs if it has to stop to a intermediate destination C, going A -> C -> B. Suppose I have a table of flight legs, with columns flight ID, origin, destination and date. An example would be:
Id - Origin - Destination - Date
1 A C d1
1 C B d2
1 B D d3
2 E F d4
I need to create two columns, one indicating the itinerary of the flight - so its value for rows with Id 1 would be A/C/B/D and for Id 2 E/F, and one indicating the number os legs each complete flight has - so, for 1, it would be 3 legs.
I could do it with a python script, but I want to solve it purely using the M language. I thought about group by id after sorting by date (to guarantee that the legs would be in order) and the defining the itinerary and number of legs for each group. The itinerary would be the first pair ORIGIN/DESTINATION concatenated (along with "/") with the remaining destinations, within each group. Can anyone help me write this in M?
custom sorting
let Source = your_table, comparer = (x, y) => if x[Date] > y[Date] then 1 else if x[Date] < y[Date] then -1 else if x[Origin] = y[Destination] then 1 else if x[Destination] = y[Origin] then - 1 else 0, g = Table.Group(Source, "Id", {{"legs", each Table.Sort(_, comparer)}}), iti = Table.AddColumn(g, "itinerary", each Text.Combine([legs][Origin] & {List.Last([legs][Destination])}, "/")), legs = Table.TransformColumns(iti, {"legs", Table.RowCount}) in legs
10 Replies
ronrsnfldSuper User
Similar algorithm, except I joined all of the Origins with the Last Destination
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYmcgTjFUitWBCIG4TiAhI7gQiOsCEjIGCxkBma5A7AYSMlGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Origin = _t, Destination = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Origin", type text}, {"Destination", type text}, {"Date", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, { {"Itinerary", (t)=> let #"Sort by Date" = Table.Sort(t,{"Date", Order.Ascending}) in Text.Combine(#"Sort by Date"[Origin] & {List.Last(#"Sort by Date"[Destination])},"/"), type text} } ) in #"Grouped Rows"- ronrsnfld
I'm not at my computer now but isn't that the same as your sample? I thought the results I showed were what you wanted. Can you clarify?
I did try it, and it did work. I didn't accept it as a solution because it doesn't handle the number of legs (although that is simple).
- ronrsnfld
- For my personal benefit, could you kindly supply a sample of data on which this fails. Thanks. Ron
- ThxAlot
A most common use case of recursive function in PQ,
let UDF_Itinerary = (orig, dest, iti, stop) => let pos = List.PositionOf(orig, stop, Occurrence.First, Comparer.OrdinalIgnoreCase) in if pos<>-1 then @UDF_Itinerary(orig, dest, iti&{stop}, dest{pos}) else iti&{stop}, Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYmelWB0IzxmIneA8JyB2AfOMgCxXIHZTio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Origin = _t, Destination = _t]), Grouped = Table.Group( Source, "ID", {"Grouped", each let orig = [Origin], dest = [Destination], iti = UDF_Itinerary(orig, dest, {orig{0}}, dest{0}) in [Iti = Text.Combine(iti, " / "), legs = List.Count(iti)-1] } ), #"Expanded Grouped" = Table.ExpandRecordColumn(Grouped, "Grouped", {"Iti", "legs"}) in #"Expanded Grouped"This solution resulted in stack overflow, unfortunately.
- ThxAlot
The stack overflow results from some itinerary like A B A C D
- AlienSx
custom sorting
let Source = your_table, comparer = (x, y) => if x[Date] > y[Date] then 1 else if x[Date] < y[Date] then -1 else if x[Origin] = y[Destination] then 1 else if x[Destination] = y[Origin] then - 1 else 0, g = Table.Group(Source, "Id", {{"legs", each Table.Sort(_, comparer)}}), iti = Table.AddColumn(g, "itinerary", each Text.Combine([legs][Origin] & {List.Last([legs][Destination])}, "/")), legs = Table.TransformColumns(iti, {"legs", Table.RowCount}) in legs
