I am trying to figure out how to go about merging two tables and only having the matching rows in the first table appear once with a unique value from the second table.
I have two tables. For the sake of this question, I put everything into one picture here. I have an Orders table which is green. I have several order numbers for specific parts for each order. Each order is sorted by a priority for the order; 1 being the highest priority which will need to be fulfilled first.
In blue is my Stock table. This is what is currently being held in the warehouse. Each part has a serial number and some parts are located on different shelf locations.
The non-colored table is what I want my end result to be using Power Query. The orders with the highest priority get filled with the first serial number available in the inventory. Then the next order is filled with the next serial number and so on until there is no inventory left; in which case the query will just show blank.
For the sake of the question, this is what the tables look like before my attempts to merge.
I have been trying all types of merges and sorting combinations but no mater what I do I end up with duplicates, entire row orders removed or incorrect priority fulfillment. I have a working formula that I use in Excel; an array formula which I can post if it will help, but since we are moving towards Power Query, I really want to learn how to go about doing this if possible. I am sure there is something easily logical about this task that I am overlooking so any assistance would help. Thank you.
