Forum Discussion
Power Query LookUp in another table
Hi all,
I have found so many (solved) posts, but I haven't managed to translate it to my situation, so I hope someone will help me.
In table IW38 I want to add a column M_Planner
That new column should look in table IW38 column Plannergroup and match that value to table PlGrp column Code
and return the value in table PlGrp column Planner
Can someone write te Mcode for this?
Hi IAM ,
You can add a custom column as below in the table IW38 in Power Query Editor, please find the details in the attachment.
= Table.AddColumn(#"Changed Type", "M_Planner", each PlGrp[Planner]{List.PositionOf(PlGrp[Code],[Plannergroup])})In addition, you can refer the following blog to achieve it, there are two methods(merge method and add a custom column method) include in this blog.
VLOOKUP in Power Query Using List Functions
Merge method
Add a custom column method
Best Regards
7 Replies
Hi IAM ,
You can add a custom column as below in the table IW38 in Power Query Editor, please find the details in the attachment.
= Table.AddColumn(#"Changed Type", "M_Planner", each PlGrp[Planner]{List.PositionOf(PlGrp[Code],[Plannergroup])})In addition, you can refer the following blog to achieve it, there are two methods(merge method and add a custom column method) include in this blog.
VLOOKUP in Power Query Using List Functions
Merge method
Add a custom column method
Best Regards
Hi, I understand these 2 methods, but what I don't understand is why you can't do the equivalent of SELECT.. WHERE.., if there is a relationship between the tables? In many cases the tables will be too dissimilar for a merge, and why should you have to use the language of lists in whats ostensibly an SQL DB?
Anonymous and amitchandak
Is there a preference as far as optimizing my query as to which method I use?
I need a single column of data from a different query. Is it more optimal to merge tables and then only expand the column that I need or do this list lookup?
I can tell you that I just tried it and after trying to load, it sat spinning at the evaluating stage for nearly an hour..
I ended up canceling and doing the merge instead which went right away.
IAM , lookup in power query means merge the tables
https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/
Merge: https://radacad.com/append-vs-merge-in-power-bi-and-power-queryIn the end, I merged my tables. This is the best way indeed 🙂
IAM , Thanks for the update.
