Power Query Column that looks up value in another table based on 2 criteria

[Jamez_P]

Hi,

Thank you in advance for any help. I spent several hours trying to work this out and have hit a dead end.

I have a table called "Planning", which contains all of our future planned project assignments on a day by day per Worker basis (it is quite large). The two relevant columns here are Project number and Worker ID. There is a second table "Cost Rate Card", which contains Project Number, Worker ID and Cost. I need to check the current planning table row for the Project Number and Worker ID, match this criteria against the Cost Rate Card Table and return the relevant cost to the newly calculated column. Instead of returning null, it should return "90". 

I am 99% certain I want this calculated in Power Query M, as we are using a Power BI Online app to distribute the info and have setup the dataflows and app to automatically update and calculate, so my understanding is, that this will be pre-calculate for the end users.  

Many thanks

[MFelix · Accepted solution]

Hi @Jamez_P ,

You can do a merge on Power Query based on several columns when you merge the two tables you just need to pay attention to the order you select the columns so that the sorting of the merge is the same.

https://community.fabric.microsoft.com/t5/Power-Query/Multiple-table-merge-with-multiple-column-cond....

[Jamez_P]

Thanks, I can't believe I forgot this was a possibility. For any other people with the same problem, you hold ctrl and select the columns in the order and little 1,2,3 etc. appear on each column in the merge window.