Forum Discussion
Solved
Power Query: Using value in first table to retrieve one from a column in another table
I am new to Power M and I have a simple problem and although I can find solution to similar more complicated ones I can't seem to adapt them to my needs.
I have a table (Table1) with some strings and numbers:
And another table (Table2) with some transactions where the JobType is repeated:
I need to get the values matching the JobType from the code column in the first table into an added column in the second table
I am struggling with the difference between columns and lists. Some of the functions I have attempted to use are producing not a list error.
Mohamed_Hanno That would be a LeftOuter merge I believe. Please see attached.
- How to get beta channel insider
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).
