Excel BI Excel/PQ Challenge 100 – Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Translate = 
    [
        Chars = Table.FromList({"A".."Z", "a".."z"}, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        Vowels = Table.PrefixColumns( Table.FromList({"A", "E", "I", "O", "U", "a", "e", "i", "o", "u"}, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "V"),
        Join = Table.Sort( Table.Join( Chars, "Column1", Vowels, "V.Column1", JoinKind.LeftOuter), {"Column1", Order.Ascending}),
        Fill = Table.RenameColumns( Table.FillDown(Join,{"V.Column1"}), {{"Column1", "Letters"}, {"V.Column1", "Vowels"}})
    ][Fill],
    
    AddIdx = Table.AddIndexColumn( Source, "Index", 1, 1),
    Split = Table.AddIndexColumn( Table.ExpandListColumn(Table.TransformColumns(AddIdx, {{"String", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "String"), "CharOrder", 1, 1),
    Join2 = Table.SelectColumns( Table.Sort( Table.Join( Split, "String", Translate, "Letters"), {"CharOrder", Order.Ascending}), {"Index", "Vowels"}),
    Group = Table.Group(Join2, {"Index"}, {{"Answer", each [Vowels]}}),
    Extract = Table.TransformColumns(Group, {"Answer", each Text.Combine(List.Transform(_, Text.From)), type text}),
    Join3 = Table.RemoveColumns( Table.Join( AddIdx, "Index", Extract, "Index"), "Index")
in
    Join3