Sep 17 2020 12:01 PM
Sep 18 2020 10:53 PM
@ShijuC3 Without seeing your data model, I can't help wondering where a new product code in the raw sales data comes from, if the code has not been created in some master product list already. But, I take your word for it that you still have a need to add new products to your "own" master table.
In Power Query, you could easily identify product codes in the sales table that are not in the master table by merging two queries with an "anti join" (left or right, depending on which query you select fist) and remove duplicates. This will then create a table of all new products that you need to copy into your master table and complete with whatever product specific details you want to store in it. (This is similar to performing e.g. a VLOOKUP of the product code in the sales table against existing product codes in the master and then filtering all the "#NA" to find the records that have no match).
Once you have added the new products to the master, refresh all queries and the "new product" table should be empty and whatever lookups you had created to the master table should now include the relevant details of all new products.