Forum Discussion

c_laurenti's avatar
c_laurenti
Frequent Visitor
3 years ago
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 ...
Need Help
Tips and Tricks
  • AlienSx's avatar
    AlienSx
    3 years ago

    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
ronrsnfld's avatar
ronrsnfld
Icon for Super User rankSuper User
3 years ago

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's avatar
ronrsnfld
Icon for Super User rankSuper User
3 years ago

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?