Merge Tables with Wildcards

[fzappa]

Hey all! Hope you are well.

I have a table with supplier's company name and another one with transactions.  The company names have wildcards for the processes that are being recorded on the transaction table. My wish is to have a merged query, that replaces the wildcard from Company for any other possible value. Is there a way I can achieve this?

Company Table

Company Name	Location
companyname1_*	USA
companyname2_*	Croatia
company_*_name3_*	Phillipines
companyname4	Brazil
companyname5_*	USA

Transactions Table

Transaction	Quantity
companyname1_sell	123
companyname2_sell	123
companyname1_sell_brazil	1
company_microsoft_name3_bought	123
companyname4	1111
companyname8_bought	1447

Desired Output

Location	Transaction	Quantity
USA	companyname1_sell	123
USA	companyname1_sell_brazil	1
Croatia	companyname2_sell	123
Phillipines	company_microsoft_name3_bought	123
Brazil	companyname4	1111

Many thanks in advance for your assistance on this topic!

[lbendlin · Accepted solution]

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"

[Omid_Motamedise]

Thanks for sharing your great solution, 

just copy and past the below code on your Power Query advance editor

let
    
    Company = 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(Company, "split", each List.Select(Text.Split([Company Name],"*"), each _<>"")),
    Transaction = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs7PLUjMq8xLzE01jC9OzclR0lEyNDJWitVBkTPCIwfRF59UlFiVCVaCrCA+NzO5KL84P60kHqTWOD4pvzQ9owS7SSYgYSBAF7dA0mViYq4UGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Transaction = _t, Quantity = _t]),
    #"Added Custom" = Table.AddColumn(Transaction, "Custom", each Table.SelectRows(Added_Custom, (x)=> List.AllTrue(List.Transform(x[split], (y)=> Text.Contains([Transaction],y) )))[Location]{0})
in
    #"Added Custom"

[Anonymous · Accepted solution]

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

[lbendlin · Accepted solution]

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"