Excel BI Power Query Challenge 42 – Brian Julius Solution

gistfile1.txt

let
    Source = Table.AddIndexColumn( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "Index", 0, 1),

    AddConsecutive = Table.AddColumn(Source, "Consecutive", each 
    [
        PriorAlphabets = try Source[Alphabets]{[Index]-1} otherwise null,
        PriorSeq =  try Source[Seq]{[Index]-1} otherwise [Alphabets],
        Consecutive = if [Alphabets] <> PriorAlphabets then "Y" else
            if [Seq] = PriorSeq + 1 then "Y" else 
            "N"
    ][Consecutive]),

    AddStreak = Table.Group(AddConsecutive, {"Alphabets", "Consecutive"}, {{"All", each _, type table [Alphabets=text, Seq=number, Index=number, Consecutive=text]}, {"Streak", each Table.RowCount(_), Int64.Type}, {"MinSeq", each List.Min([Seq]), type number}, {"MaxSeq", each List.Max([Seq]), type number}}, GroupKind.Local),
    Expand = Table.ExpandTableColumn(AddStreak, "All", {"Seq", "Index"}, {"Seq", "Index"}),
    
    AddGrouping = Table.AddColumn(Expand, "Grouping", each 
    [
        SubseqAlphabets = try Expand[Alphabets]{[Index] + 1} otherwise null,
        SubseqConseq = try Expand[Consecutive]{[Index] + 1} otherwise null,
        Test = if [Consecutive] = "Y" then Text.Combine( { Text.From( [Streak]), Text.From( [MinSeq]) }, null ) else 
        if List.AllTrue( { [Alphabets] = SubseqAlphabets, SubseqConseq = "Y" }) then Text.Combine( { Text.From( Expand[Streak]{[Index]+1} ), Text.From( Expand[MinSeq]{[Index] + 1}) }, null ) else 
        Text.Combine( { Text.From( [Streak] ), Text.From( [Seq])}, null)
    ][Test]),
    
    Group = Table.AddIndexColumn( Table.Group(AddGrouping, {"Alphabets", "Grouping"}, {{"From", each List.Min([Seq]), type nullable number}, {"To", each List.Max([Seq]), type nullable number}}), "Index", 0, 1),
    
    AddMissing = Table.AddColumn(Group, "AddMissing", each [
        PrevAlpha = Group[Alphabets]{[Index] - 1},
        PrevTo = Group[To]{[Index] - 1},
        Test2 = [Alphabets] = PrevAlpha,
        Begin = PrevTo + 1,
        End = Group[From]{[Index]} - 1,
        Missing = try if Test2 = true then {Begin..End} else null otherwise null
    ][Missing]),
    
    Extract = Table.TransformColumns(AddMissing, {"AddMissing", each try Text.Combine(List.Transform(_, Text.From), ",") otherwise null, type text}),
    Clean = Table.RenameColumns( Table.RemoveColumns( Extract, {"Grouping", "Index"}), {"AddMissing","Missing"})
in
    Clean