Brian Julius Solution to Excel BI Challenge 561 - Maximum Profit

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ToRows = Table.FromList( Table.ToRows( Source ), Splitter.SplitByNothing(), {"Trans"}),
    BuySell = Table.AddColumn(ToRows, "BuySell", each [ 
        a = [Trans],
        b = List.Transform(a, each List.PositionOf(a, _)),
        c = List.Transform(a, each 1),
        d = Table.FromColumns( {a, b, c}, {"Buy", "BuyPos", "B1"}),
        e = Table.FromColumns( {a, b, c}, {"Sell", "SellPos", "S1"}), 
        f = Table.SelectRows( Table.Join( d, "B1", e, "S1" ), each [BuyPos] < [SellPos]),
        g = Table.SelectColumns( f, {"Buy", "Sell"}),
        h = Table.AddColumn( g, "Profit", each [Sell] - [Buy]),
        i = List.Max(h[Profit]),
        j = Table.PrefixColumns( Table.SelectRows( h, each [Profit] = i), "x")
   ][j]),
    SelCols = Table.SelectColumns( BuySell, "BuySell"),
    Expand = Table.ExpandTableColumn(SelCols, "BuySell", {"x.Buy", "x.Sell", "x.Profit"}, {"x.Buy", "x.Sell", "x.Profit"}),
    AddNegClean = Table.AddColumn(Expand, "NClean", each [ 
        a = Expand,
        b = Table.AddColumn(a, "Buy", each if [x.Profit] < 0 then "NP" else Text.From ([x.Buy])),
        c = Table.AddColumn(b, "Sell", each if [x.Profit] < 0 then "NP" else Text.From( [x.Sell] )),
        d = Table.AddColumn(c, "Profit", each if [x.Profit] < 0 then "NP" else Text.From ([x.Profit])),
        e = Table.SelectColumns(d, {"Buy", "Sell", "Profit"})
    ][e]),
    Ans = Table.First( AddNegClean)[NClean]
in
    Ans