Forum Discussion
Solved
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 ...
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
ThxAlot
Super User
Super UserA 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.
