Power Query LookUp in another table

[IAM]

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?

[Anonymous · Accepted solution]

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

[michaelu1]

@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?

[michaelu1]

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.

[Walt1010]

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?

[amitchandak]

@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-query

[IAM]

In the end, I merged my tables. This is the best way indeed 🙂

[amitchandak]

@IAM , Thanks for the update.