Find flight itinerary from flight legs

[c_laurenti]

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?

[AlienSx · Accepted solution]

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]

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"

[c_laurenti]

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

[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?

[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"

[c_laurenti]

This solution resulted in stack overflow, unfortunately.

[ThxAlot]

The stack overflow results from some itinerary like A B A C D

[ronrsnfld]

Did you ever try the code I provided?

[ThxAlot]

Your code ... even can't tackle simple situation like this