Forum Discussion
Power Query: Using value in first table to retrieve one from a column in another table
Mohamed_Hanno That would be a LeftOuter merge I believe. Please see attached.
Mohamed_Hanno That would be a LeftOuter merge I believe. Please see attached.
Thanks Riny_van_Eekelen for your reply but the reason I wanted to do it in M code was that my actual requirements is much more sophisticated. The first table has repeated values in the code column and a third column with values something like this:
I want to get the highest value that matches the Job Type and add it to the second table which means that the relation is many to many and that I need to use some function that retrieves the max value. Can this be done without coding?
My apology for not explaining all this the first time but I thought it would be simpler to take it one step at a time.
"I want to do it in M code"
I presume you mean that you want to use Power Query.
"Can this be done without coding?"
Well, Power Query automatically creates M code for you if you are not dealing with very complex matters. So, yes you can achieve quite a lot without coding. Just push the correct buttons in the correct order.
And as far as your question is concerned, no I don't get it. Contrary to your idea to take it step-by-step, it's better to give the complete picture from the start. How does the raw data look like and how should the end result look like? Do not overly simplify the matter. And perhaps you don't need PQ at all. Then, it's important to know your Excel version and platform (PC or Mac).
To answer your questions:
- I need to use Power Query because I am getting the data from a company database and need to maintain a dynamic link to update the data.
- I am using Excel 365 on Windows
- Regarding the data:
This is the first table that includes the codes that I want to add to the second table based on two criteria (highest code value, matching Job Type:This is the second table where I want to add the highest codes wherever the Job Title matches:
