Forum Discussion

fzappa's avatar
fzappa
New Member
1 year ago
Solved

Merge Tables with Wildcards

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 NameLocation
companyname1_*USA
companyname2_*Croatia
company_*_name3_*Phillipines
companyname4Brazil
companyname5_*USA

 

Transactions Table

 

TransactionQuantity
companyname1_sell123
companyname2_sell123
companyname1_sell_brazil1
company_microsoft_name3_bought123
companyname41111
companyname8_bought1447

 

Desired Output

LocationTransactionQuantity
USAcompanyname1_sell123
USAcompanyname1_sell_brazil1
Croatiacompanyname2_sell123
Phillipinescompany_microsoft_name3_bought123
Brazilcompanyname41111


Many thanks in advance for your assistance on this topic!

 

Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    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"

     

     

  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    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"

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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

     

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Super User
    1 year ago

    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"