Excel BI Excel/Power Query Challenge 125 - Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    MorseTable = 
    [
        Source = Table.ToColumns( Excel.CurrentWorkbook(){[Name="Table3"]}[Content]),
        Letters = List.Union( {Source{0}, Source{2}, Source{4}}),
        Code = List.Union({ Source{1}, Source{3}, Source{5}}),
        TabFromCols = Table.FromColumns({Letters, Code}, {"Letters", "Code"}),
        Replace = Table.ReplaceValue(TabFromCols,null," ",Replacer.ReplaceValue,{"Letters"}),
        Replace2 = Table.ReplaceValue(Replace,null,"/",Replacer.ReplaceValue,{"Code"}),
        Dedupe = Table.Distinct(Replace2, {"Code"}),
        ReType = Table.TransformColumnTypes(Dedupe,{{"Letters", type text}})
    ][ReType],
    TextToList = Table.AddColumn(Source, "Char", each Text.ToList([String])),
    Expand = Table.ExpandListColumn(TextToList, "Char"),
    Upper = Table.TransformColumns(Expand,{{"Char", Text.Upper, type text}}),
    AddIdx = Table.AddIndexColumn(Upper, "Index", 1, 1, Int64.Type),
    Join = Table.Join( AddIdx, "Char", MorseTable, "Letters"),
    Sort = Table.Buffer( Table.Sort(Join,{{"Index", Order.Ascending}})),
    Group = Table.Group(Sort, {"String"}, {"All", each [Code]}),
    Extract = Table.TransformColumns(Group, {"All", each Text.Combine(List.Transform(_, Text.From), " "), type text})
in
    Extract