Excel BI Power Query Challenge 21 – Brian Julius Solution

gistfile1.txt

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZDBCoMwDIbfpWcDaapbd9x9ynCnUTxUEBScgvr+rNsgRtZCLuHnS77EOfV4DVuvMkVIBJiDwdA8VZM5dRtWz0kozckR0qApNNU/lP+SGGTA7PPuvh1njoqwLU5pBDIJyibVz0D7qnpuu2UTA79nVZEMT0GFues0dUJeGJZ+WXs/iqOF4wGzQDYh8pG8pDLNv2re", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Won = _t]),
    DateType = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Sort = Table.Sort(DateType,{{"Name", Order.Ascending}, {"Date", Order.Ascending}}),
    Group = Table.Group(Sort, {"Name", "Won"}, {{"AllData", each Table.AddIndexColumn( Table.Sort(_, {{"Date", 0}}), "rowindexinsubset",1,1 ), type table}}, GroupKind.Local),
    Expand = Table.ExpandTableColumn(Group, "AllData", {"Date", "rowindexinsubset"}, {"Date", "rowindexinsubset"}),
    Group2 = Table.Group(Expand, {"Name"}, {{"MaxStreak", each List.Max([rowindexinsubset]), type number}, {"AllData", each _, type table [Name=nullable text, Won=nullable text, Date=date, rowindexinsubset=number]}}),
    FilterStreaks = Table.SelectRows(Group2, each ([MaxStreak] = 3)),
    Expand2 = Table.ExpandTableColumn(FilterStreaks, "AllData", {"Won", "Date", "rowindexinsubset"}, {"Won", "Date", "rowindexinsubset"}),
    FilteLosses = Table.SelectRows(Expand2, each ([Won] = "Y")),
    Group3 = Table.Group(FilteLosses, {"Name"}, {{"AllData", each _, type table [Name=nullable text, MaxStreak=number, Won=nullable text, Date=nullable date, rowindexinsubset=nullable number]}}),
    Streaklength = Table.AddColumn(Group3, "StreakLength", each Number.From( List.Max( [AllData][Date] )) - Number.From( List.Min( [AllData][Date] ))),
    FiltererStreakLength = Table.SelectRows(Streaklength, each [StreakLength] <= 180),
    Expand3 = Table.ExpandTableColumn(FiltererStreakLength, "AllData", {"Date"}, {"Date"}),
    Clean = Table.RemoveColumns(Expand3,{"StreakLength"})
in
    Clean