Brian Julius Solution to Oz 14_06_JerseyNumbers Challenge

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T1 = Table.SelectColumns( Source, {"Player", "Position", "Jersey # Wanted"}),
    T3 = Table.SelectColumns( Source, {"Taken", "Retired" }),
    T2 = Table.SelectColumns( Source, {"Numbers", "QB", "RB/WR/TE", "OL", "DL", "LB", "DB", "K/P" }),
    RemBlank = Table.SelectRows(T2, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    NumList = Table.AddColumn(RemBlank, "NumList", each [
        a = Text.Split( [Numbers], "–"),
        b = Number.From( a{0} ),
        c = Number.From( a{1} ),
        d = {b..c}
    ][d] ),
    ExpandNums = Table.RemoveColumns( Table.ExpandListColumn(NumList, "NumList"), "Numbers"),
    Unpivot = Table.UnpivotOtherColumns(ExpandNums, {"NumList"}, "Attribute", "Value"),
    EligibleNums = List.Distinct( List.RemoveMatchingItems( Unpivot[NumList], List.Combine(  { List.RemoveNulls( T3[Taken] ), List.RemoveNulls( T3[Retired] )} ))),
    RemIneligNums = Table.SelectRows( Unpivot, each List.Contains( EligibleNums, [NumList])),
    Join = Table.Join( T1, {"Jersey # Wanted", "Position"}, RemIneligNums, {"NumList", "Attribute"}, JoinKind.LeftOuter),
    ReplNulls = Table.RemoveColumns( Table.ReplaceValue(Join,null,"No",Replacer.ReplaceValue,{"Value"}), {"NumList", "Attribute"}),
    Group = Table.Group(ReplNulls, {"Jersey # Wanted", "Value"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Player=text, Position=text, #"Jersey # Wanted"=number, Value=text]}}),
    AddBid = Table.RemoveColumns( Table.AddColumn(Group, "BidTbl", each Table.AddColumn( [All], "Bid", each Number.Round( Number.RandomBetween( 0, 1000000), 0))), "All"),
    ExpandBidTbl = Table.ExpandTableColumn(AddBid, "BidTbl", {"Player", "Position", "Bid"}, {"Player", "Position", "Bid"}),
    Regroup = Table.Group(ExpandBidTbl, {"Jersey # Wanted"}, {{"MaxBid", each List.Max([Bid]), type number}, {"All", each _, type table [#"Jersey # Wanted"=number, Value=text, Count=number, Player=text, Position=text, Bid=number]}}),
    Expand = Table.ExpandTableColumn(Regroup, "All", {"Value", "Count", "Position", "Bid", "Player"}, {"Value", "Count", "Position", "Bid", "Player"}),
    AddOutcome = Table.AddColumn(Expand, "Outcome", each if [Value] = "No" then "No" else 
        if [Count] = 1 then "Yes" else 
        if [Bid] = [MaxBid] then "Yes (High bidder)" else 
        "No (Not high bidder)"),
    Reorder = Table.ReorderColumns(AddOutcome,{"Player", "Position", "Jersey # Wanted", "Outcome", "MaxBid", "Value", "Count", "Bid"}),
    Clean = Table.Sort( Table.RemoveColumns(Reorder,{"MaxBid", "Value", "Count", "Bid"}), {"Player", Order.Ascending})
in
    Clean