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

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?