Excel BI Challenge 131 - Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    
    StringTable = [
    StrTable = Table.AddColumn( Table.SelectColumns( Source, "Masked String"), "Chars", each Text.ToList( [#"Masked String"] )),
    AddPositions = Table.AddColumn(StrTable, "Position", each List.Positions([Chars])),
    AddZipPositions = Table.RemoveColumns( Table.AddColumn(AddPositions, "AsteriskPositions", each List.Zip( {[Chars], [Position] })), {"Chars", "Position"}),
    Expand = Table.ExpandListColumn(AddZipPositions, "AsteriskPositions"),
    Extract = Table.TransformColumns(Expand, {"AsteriskPositions", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    Split = Table.SplitColumn(Extract, "AsteriskPositions", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"AsteriskPositions.1", "AsteriskPositions.2"}),
    ReType = Table.TransformColumnTypes(Split,{{"AsteriskPositions.1", type text}, {"AsteriskPositions.2", Int64.Type}}),
    AddAsteriskPos = Table.AddColumn(ReType, "AsteriskPos", each if [AsteriskPositions.1] = "*" then [AsteriskPositions.2] else null),
    Filter = Table.SelectColumns( Table.SelectRows(AddAsteriskPos, each ([AsteriskPos] <> null)), {"Masked String", "AsteriskPos"}),
    Final = Table.AddIndexColumn( Filter, "Index", 1, 1)
    ][Final],

    ReplTable = 
    [
        a = Table.AddColumn( Source, "ReplChars", each Text.ToList( [Chars])),
        b = Table.ExpandListColumn( a, "ReplChars"),
        c = Table.RenameColumns( Table.AddIndexColumn( b, "Index2", 1, 1), {"Masked String", "MaskedString2"})

    ][c],
    NewTable = Table.RenameColumns( Table.SelectColumns( Table.Join( StringTable, "Index", ReplTable, "Index2", JoinKind.Inner), {"Masked String", "AsteriskPos", "ReplChars", "Index"}), {"Masked String", "MaskedString"}),
    FinalTable = [
        
        x = Table.AddColumn( Table.SelectColumns( Source, "Masked String"), "Chars", each Text.ToList( [#"Masked String"] )),
        y = Table.AddColumn(x, "Position", each List.Positions([Chars]))
   ][y],
    AddZipList = Table.SelectColumns( Table.AddColumn(FinalTable, "ZipList", each List.Zip( {[Chars], [Position]})), {"Masked String", "ZipList"}),
    Expand = Table.ExpandListColumn(AddZipList, "ZipList"),
    Extract = Table.TransformColumns(Expand, {"ZipList", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split = Table.SplitColumn(Extract, "ZipList", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"ZipList.1", "ZipList.2"}),
    ReType = Table.AddIndexColumn( Table.RenameColumns(  Table.TransformColumnTypes(Split,{{"ZipList.1", type text}, {"ZipList.2", Int64.Type}}), {"ZipList.2", "Pos"}), "SortIndex", 1, 1),
    SolutionTable = Table.SelectColumns( Table.Sort( Table.Join( ReType, {"Masked String", "Pos"}, NewTable, {"MaskedString", "AsteriskPos"}, JoinKind.LeftOuter), {"SortIndex", Order.Ascending}), {"Masked String", "ZipList.1", "ReplChars"}),
    #"AddReplace*" = Table.SelectColumns( Table.AddColumn(SolutionTable, "Replace*", each if [ZipList.1] = "*" then [ReplChars] else [ZipList.1]), {"Masked String", "Replace*"}),
    Group = Table.Group(#"AddReplace*", {"Masked String"}, {{"All", each _, type table [Masked String=text, #"Replace*"=text]}}),
    SelectCols = Table.RemoveColumns( Table.AddColumn(Group, "Select", each Table.ToColumns( Table.SelectColumns( [All], "Replace*" ))), "All"),
    Expand2 = Table.ExpandListColumn(SelectCols, "Select"),
    ExtractNClean = Table.RenameColumns( Table.TransformColumns(Expand2, {"Select", each Text.Combine(List.Transform(_, Text.From)), type text}), {"Select", "Answer"})
in
    ExtractNClean

I would like to apologize in advance for the utter insanity of this solution...