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