Forum Discussion

ABR002's avatar
ABR002
Icon for Helper I rankHelper I
2 years ago
Solved

Match Wildcard via Relationship or Merge

So I've found some topics on this subject but none seem to pertain to my exact situation. I have 2 tables with codes in ABC-####-##### format. On one there is only one value per cell, but on the othe...
Need Help
  • Omid_Motamedise's avatar
    Omid_Motamedise
    2 years ago

    Consider this table as your second table in power query.



    select column code and go to transform tab and pick split column by delimiter and use "," as delimiter to reach the next table

     

     

    now right click on the column row number table 2 and pick unpivot other columns to reach the next table

     

     

     

    you do not need column attribute, by reming this, you reached a tablel apporopriate for merging by the first om

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

Hi ABR002, it is not a mistake to split by delimiter and merge (which creates duplicated rows), but you can delete them afterwards. You can compare query speed with this (without merge):

 

Output

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tci5DQAgDATBXi7GEhw2OVCG5f7b4ElgNxt3FCT0MYU5U1jVGiI5+LPa/nJ9bGKny/q4CU+IWA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Row Number" = _t, #"Table 1 Code" = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZYxBDoAgDMC+YnbeEhgb4FF8BuH/3zDTZM7YWw/tnJAB4RgncUpMxcDNXQx3JS6i1T3T3ltVWDiB341QM0L23fCzubMSs26ETI1/ti4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Row Number" = _t, #"Table 2 Code" = _t]),
    T2_Ad_Helper = Table.AddColumn(Table2, "Helper", each List.Transform(Text.Split([Table 2 Code], ","), Text.Trim), type list),
    T2_Buffered = Table.Buffer(T2_Ad_Helper),
    T1_Ad_T2 = Table.AddColumn(Table1, "T2", each Table.SelectRows(T2_Buffered, (x)=> List.Contains(x[Helper], [Table 1 Code])), type table),
    ExpandedT2 = Table.ExpandTableColumn(T1_Ad_T2, "T2", {"Row Number", "Table 2 Code"}, {"Table2 Row Number", "Table 2 Code"}),
    FilteredRows = Table.SelectRows(ExpandedT2, each ([Table2 Row Number] <> null))
in
    FilteredRows