Excel BI Excel Challenge 103 - Brian Julius Solution

gistfile1.txt

let
    Source = Table.AddIndexColumn( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "Index", 1, 1),
    SplitToRows = Table.AddIndexColumn( Table.ExpandListColumn(Table.TransformColumns(Source, {{"Text", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Text"), "SortOrder", 1, 1),
    Target = Table.AddIndexColumn( Table.FromList( {"A".."Z", "a".."z"}, Splitter.SplitByNothing(), {"Letters"}, null, ExtraValues.Error), "Position", 1, 1),
    JoinPos = Table.RemoveColumns( Table.Sort( Table.Join(SplitToRows, "Text", Target, "Letters", JoinKind.LeftOuter), {"SortOrder", Order.Ascending}), "Letters"),
    AddModShift = Table.AddColumn(JoinPos, "ModShift", each 
        if List.Contains( {"0".."9"}, [Text] ) then  Number.Mod( [Shift], 10) else 
        if [Position] = null then null else 
        Number.Mod( [Shift] + [Position], 52)),
    JoinShifted = Table.Sort( Table.Join( AddModShift, "ModShift", Table.PrefixColumns( Target, "T"), "T.Position", JoinKind.LeftOuter), {"SortOrder", Order.Ascending}),
    AddShiftedChar = Table.AddColumn(JoinShifted, "ShiftedChar", each 
        if List.Contains( {"0".."9"}, [Text] ) then Number.Mod( Number.From( [Text] ) + [ModShift], 10) else 
        if [Position] = null then [Text] else 
        if [Position] <= 26 then Text.Upper( [T.Letters]) else 
        Text.Lower( [T.Letters])
    ),
    Group = Table.Group(AddShiftedChar, {"Index"}, {"All", each [ShiftedChar]}),
    Extract = Table.TransformColumns(Group, {"All", each Text.Combine(List.Transform(_, Text.From)), type text}),
    JoinNClean = Table.RenameColumns( Table.RemoveColumns( Table.Join( Source, "Index", Extract, "Index"), {"Index"}), {"All", "Answer Expected"})
in
    JoinNClean