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 trans...
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

     

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"