Forum Discussion

Aggro's avatar
Aggro
Frequent Visitor
4 years ago
Solved

Lookupvalue Matching / Merging in Multiple Columns

Hi

 

I want to know if my order id column from table 1 matches another table's order id columns (there are 3 columns), and return with the value. Keep in mind that table 2 would have like over 10M data, running merge (left join) is a bit painful, and doing more than 2 left join merges are so slow, not sure if there is a better way. 

Thank you

 

 

Table 1  Table 2     Table 1 
Order ID  Order ID -1Order ID -2Order ID -3   Order IDOrder_id (from table 2)
A  A2q452qrrrqwer2435ds   AOrder ID -1
B  Basfsadq2134dsfg   BOrder ID -1
C  Casfd235532423waef   COrder ID -1
D  234qwsddDsadfsafsfer   DOrder ID -2
E  asdfr42rEasdfasdf   EOrder ID -2
F  qwedsadFasdf   FOrder ID -2
G  wqredfsafGsfa   GOrder ID -2
H  wqrwrsafdH   HOrder ID -3
I  asdfasdfasdfI   IOrder ID -3
J  asfdasfdsJ   JOrder ID -3
K  asdfasdf23K   KOrder ID -3
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    In Power Query:

    Table 1

     Table 2:

     

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY47DgMxCETv4nqbgH2A/H9HWG1hCZPaUPj6AdZJgUDzmIF1Tce0JOi5QBcRm/toAhkLadqWNZ1MqspayRkcMJPyJ9B5RwRYio0IGXDUxgEBcx9K7rpYmd8yWLlJ4KrEksEPXiOG2CuYfUD7vdtEIY8uLUJMvHsk158+JE6wex7//HDOgCU9px5L3tT66788FwFteKdt+wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order ID -1" = _t, #"Order ID -2" = _t, #"Order ID -3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order ID -1", type text}, {"Order ID -2", type text}, {"Order ID -3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value")
    
in
    #"Unpivoted Columns"

     

     

     

    Finally, Table 3

     

     

     

    let
    Source = Table.NestedJoin(Table1, {"Order ID"}, Table2, {"Value"}, "Table2 (2)", JoinKind.LeftOuter),
    #"Expanded Table2 (2)" = Table.ExpandTableColumn(Source, "Table2 (2)", {"Attribute"}, {"Table2 (2).Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Table2 (2)",{{"Table2 (2).Attribute", "Order ID Detail"}})
in
    #"Renamed Columns"

     

     

     

     

    I've attached the sample PBIX file

5 Replies

  • ddpl's avatar
    ddpl
    Solution Sage
    4 years ago

    Aggro ,

     

    Step 1 : create index column in both table 1 and 2 in power query and join them with index.

     

    Step 2 : Create new calculated column in table 1

     

    Column = IF(Table1[Order ID] = RELATED(Table2[Order ID -1]),"Order ID-1",
             IF(Table1[Order ID] = RELATED(Table2[Order ID -2]),"Order ID-2","Order ID-3"))
  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    Can you please clarify what you mean by "and return with the value"? What value must be returned?

    Also, what is the expected outcome if one row in table 2 has valid values from table 1, or is that not possible?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Aggro ,

    Here are the steps you can follow：

    1. Create calculated column.

     

    Order_id (from table 2) = 
var _ID1=SELECTCOLUMNS('Table2',"ID1",'Table2'[Order ID -1])
var _ID2=SELECTCOLUMNS('Table2',"ID2",'Table2'[Order ID -2])
var _ID3=SELECTCOLUMNS('Table2',"ID3",'Table2'[Order ID -3])
return
SWITCH(
    TRUE(),
    'Table1'[Order ID] in _ID1 ,
    "Order ID -1",
       'Table1'[Order ID] in _ID2 ,
    "Order ID -2", 
    'Table1'[Order ID] in _ID3 ,
    "Order ID -3")

     

    2. Result:

    If you need pbix, please click here.

    Best Regards,

    Liu Yang

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

    • Aggro's avatar
      Aggro
      Frequent Visitor
      4 years ago

      Thank you, this is under the DAX. Is there a way to do those in the power query ? Because if we do DAX, that would mean I have to load all those data. 

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    In Power Query:

    Table 1

     Table 2:

     

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY47DgMxCETv4nqbgH2A/H9HWG1hCZPaUPj6AdZJgUDzmIF1Tce0JOi5QBcRm/toAhkLadqWNZ1MqspayRkcMJPyJ9B5RwRYio0IGXDUxgEBcx9K7rpYmd8yWLlJ4KrEksEPXiOG2CuYfUD7vdtEIY8uLUJMvHsk158+JE6wex7//HDOgCU9px5L3tT66788FwFteKdt+wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order ID -1" = _t, #"Order ID -2" = _t, #"Order ID -3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order ID -1", type text}, {"Order ID -2", type text}, {"Order ID -3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value")
    
in
    #"Unpivoted Columns"

     

     

     

    Finally, Table 3

     

     

     

    let
    Source = Table.NestedJoin(Table1, {"Order ID"}, Table2, {"Value"}, "Table2 (2)", JoinKind.LeftOuter),
    #"Expanded Table2 (2)" = Table.ExpandTableColumn(Source, "Table2 (2)", {"Attribute"}, {"Table2 (2).Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Table2 (2)",{{"Table2 (2).Attribute", "Order ID Detail"}})
in
    #"Renamed Columns"

     

     

     

     

    I've attached the sample PBIX file