Lookupvalue Matching / Merging in Multiple Columns

[Aggro]

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 -1	Order ID -2	Order ID -3				Order ID	Order_id (from table 2)
A			A	2q452qrrr	qwer2435ds				A	Order ID -1
B			B	asfsad	q2134dsfg				B	Order ID -1
C			C	asfd2355	32423waef				C	Order ID -1
D			234qwsdd	D	sadfsafsfer				D	Order ID -2
E			asdfr42r	E	asdfasdf				E	Order ID -2
F			qwedsad	F	asdf				F	Order ID -2
G			wqredfsaf	G	sfa				G	Order ID -2
H			wqrwr	safd	H				H	Order ID -3
I			asdfasdf	asdf	I				I	Order ID -3
J			asfd	asfds	J				J	Order ID -3
K			asdf	asdf23	K				K	Order ID -3

[PaulDBrown · Accepted solution]

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

[v-yangliu-msft]

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]

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]

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?

[ddpl]

@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"))