Merge Tables with Wildcards

Use Table.AddColumns with a custom column generator.  Here's a first stab for single patterns, adjust as needed.  Note that pattern order is not checked.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs7PLUjMq8xLzE01jNdS0lEKDXZUitVBkTACSzgX5SeWZCYiS8ZrxYPkjcHyARmZOTmZBZl5qcXoBpgApZ2KEqsyc9BlTBF2xgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Company Name" = _t, Location = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", (k)=> let terms=List.RemoveItems(Text.Split(k[Company Name],"*"),{""}) in   
Table.SelectRows(Transactions,each Text.Contains([Transaction],terms{0}))
),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Transaction", "Quantity"}, {"Transaction", "Quantity"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Quantity] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Company Name"})
in
    #"Removed Columns"

Hi fzappa ,
Thanks for lbendlin reply,you can also try this code

let
    CompanyTable = #"Company Table",
    TransactionTable = #"Transactions Table",
    SplitCompanyName = Table.SplitColumn(CompanyTable, "Company Name", Splitter.SplitTextByDelimiter("_*", QuoteStyle.Csv), {"Part1", "Part2", "Part3"}),
    AddMatchRule = Table.AddColumn(SplitCompanyName, "MatchRule", each Text.Combine(List.Select({[Part1], [Part2], [Part3]}, each if _ <> null then _ else ""), "*")),
    DuplicateTransaction = Table.DuplicateColumn(TransactionTable, "Transaction", "OriginalTransaction"),
    SplitTransaction = Table.SplitColumn(DuplicateTransaction, "Transaction", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Part1", "Part2", "Part3"}),
    MergedTables = Table.NestedJoin(SplitTransaction, {"Part1"}, AddMatchRule, {"Part1"}, "CompanyTable", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(MergedTables, "CompanyTable", {"Location"}),
    FilteredTable = Table.SelectRows(ExpandedTable, each [Location] <> null),
    RemovedColumns = Table.RemoveColumns(FilteredTable, {"Part1", "Part2", "Part3"})
in
    RemovedColumns

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly