Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Aggro
Frequent Visitor

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
1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

In Power Query:

Table 1Table 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"

 

 

T2.png

 

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"

 

 

 

T3.png

 

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

5 REPLIES 5
PaulDBrown
Community Champion
Community Champion

In Power Query:

Table 1Table 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"

 

 

T2.png

 

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"

 

 

 

T3.png

 

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1658195931801.png

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. 

PaulDBrown
Community Champion
Community Champion

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?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






ddpl
Solution Sage
Solution Sage

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.