Excel BI Excel Challenge 102 - Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.RenameColumns( Table.SplitColumn(Source, "Names", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Names.1", "Names.2"}), {{"Names.1", 
"FirstName"}, {"Names.2", "Surname"}}),

    Vowels = {"A", "E", "I", "O", "U", "a", "e", "i", "o", "u"},
    Consanants = List.RemoveMatchingItems( {"A".."Z", "a".."z"}, Vowels),
    
    Surname = Table.AddColumn(Split, "SurCode", each [ 
            SurCons =  Text.Upper(Text.Remove( [Surname], Vowels)),
            SurVowels = Text.Upper(Text.Remove(  [Surname], Consanants)),
            SurConcat = SurCons & SurVowels,
            SurPadded = Text.PadEnd( SurConcat, 3, "X"),
            SurFinal = Text.Start( SurPadded, 3)
        ][SurFinal]),
    
    FirstName = Table.AddColumn(Surname, "FirstCode", each [
        FirstCons =  Text.Upper(Text.Remove( [FirstName], Vowels)),
        FirstConsToList = Text.ToList( FirstCons),
        FirstConsCount = List.Count( FirstConsToList),
        FinalCons = if FirstConsCount = 3 then Text.Start(FirstCons, 3) else 
            if FirstConsCount > 3 then FirstConsToList{0} & FirstConsToList{1} & FirstConsToList{3} else
            FirstCons,
        FirstVowels = Text.Upper(Text.Remove(  [FirstName], Consanants)),
        FirstConcat = FirstCons & FirstVowels,
        FirstPadded = Text.PadEnd( FirstConcat, 3, "X"),
        FirstFinal = Text.Start( FirstPadded, 3)
    ][FirstFinal]),
    
    MergeCode = Table.CombineColumns(FirstName,{"SurCode", "FirstCode"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Code"),
    MergeName = Table.CombineColumns(MergeCode,{"FirstName", "Surname"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Names")
in
    MergeName