Excel BI Power Query Challenge 29 – Brian Julius Solution

gistfile1.txt

let
    Source = RepeatsRaw,
    Unpivot = Table.TransformColumnTypes( Table.RenameColumns( Table.RemoveColumns( Table.UnpivotOtherColumns(Source, {"Year"}, "Attribute", "Value"), "Attribute"), {"Value", "Team"}), {"Year", Int64.Type}),
    Sort = Table.Sort(Unpivot,{{"Team", Order.Ascending}, {"Year", Order.Ascending}}),
    Group = Table.Group(Sort, {"Team"}, {{"All", each _, type table [Year=nullable number, Team=text]}}),
    AddIdx0 = Table.RemoveColumns( Table.AddColumn(Group, "Idx0", each Table.AddIndexColumn([All], "Index0", 0, 1)), "All"),
    Expand0 = Table.ExpandTableColumn(AddIdx0, "Idx0", {"Year", "Index0"}, {"Year", "Index0"}),
    AddIdx1 = Expand0,
    Expand1 = Table.RenameColumns( Table.TransformColumns(AddIdx1, {{"Index0", each _ + 1, type number}}), {{"Index0", "Index1"},{"Year", "YearPrev"},{"Team", "Team1"}}),
    Join = Table.Join( Expand0, {"Team", "Index0"}, Expand1, {"Team1", "Index1"}, JoinKind.LeftOuter),
    Choose = Table.SelectColumns(Join,{"Team", "Year", "Team1", "YearPrev"}),
    Gap = Table.AddColumn(Choose, "Gap", each [Year] - [YearPrev], type number),
    MaxGap = Table.Group(Gap, {"Team"}, {{"All", each _, type table [Team=text, Year=number, Team1=nullable text, YearPrev=nullable number, Gap=number]}, {"MaxGap", each List.Max([Gap]), type number}}),
    FilterNulls = Table.SelectRows(MaxGap, each ([MaxGap] <> null)),
    Expand = Table.SelectRows( Table.ExpandTableColumn(FilterNulls, "All", {"Year", "YearPrev", "Gap"}, {"Year", "YearPrev", "Gap"}), each [Gap] = [MaxGap]),
    Concat = Table.AddColumn(Expand, "Concat", each Text.From( [YearPrev] ) &"-"& Text.From( [Year] )),
    Choose2 = Table.SelectColumns(Concat,{"Team", "MaxGap", "Concat"}),
    Group2 = Table.Group(Choose2, {"Team", "MaxGap"}, {{"All", each _, type table [Team=text, MaxGap=number, Concat=text]}}),
    ConcatYears = Table.RemoveColumns( Table.AddColumn(Group2, "Years", each Text.Combine( [All][Concat], ", ")), "All"),
    Sort2 = Table.Sort(ConcatYears,{{"MaxGap", Order.Descending}, {"Team", Order.Ascending}})
in
    Sort2