Excel BI Challenge 33 – Brian Julius solution

gistfile1.txt

let
   
    Source = Table.RenameColumns( Table.ReorderColumns( Table.AddIndexColumn( Table.FromList({"A".."Z"}, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Index", 1, 1 ), {"Index", "Column1"}), {"Column1", "Letters"}),
    CustIdx = Table.AddIndexColumn( Table.FromList({"A".."Z"}, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Index", 1, 1 ),
    AddConcat = Table.SelectColumns( Table.AddColumn(CustIdx, "Concat", each "Column" & Text.From( [Index] )), "Concat"),
    Crossjoin = Table.ExpandTableColumn( Table.AddColumn(Source, "Custom", each AddConcat), "Custom", {"Concat"}, {"Concat"}),
    Split = Table.SplitColumn(Table.DuplicateColumn(Crossjoin, "Concat", "Concat - Copy"), "Concat - Copy", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Concat - Copy.1", "Concat - Copy.2"}),
    NumFrom = Table.TransformColumnTypes(Split,{{"Concat - Copy.2", Int64.Type}}),
    TestCol = Table.SelectColumns( Table.AddColumn(NumFrom, "Test", each if [Index] >= [#"Concat - Copy.2"] then 
[Letters] & "-" & Text.PadStart( Text.From( [#"Concat - Copy.2"] ), 2, "0") else null), {"Index", "Concat", "Test"}),
    Pivot = Table.RemoveColumns( Table.Pivot(TestCol, List.Distinct(TestCol[Concat]), "Concat", "Test"), "Index")
in
   Pivot