Adding a column with custom values to a Power Query

[bartvana]

I have in Sheet1 a table tProducts with products and quantities sold from the last 6 months (coming from SQL query from the company ERP).

I now want to make a custom table tSS where I assign to each product a binary "strategic stock" value: 1 or 0. The product list in this table needs to come from tProducts (ERP) and update automatically, so that when new products are added in tProducts, they appear in tSS (without a 0 or 1 value of course, since I will add these manually).

Finally, I want to combine the two tables in a third table tResult, where I want only the products which have a tSS "strategic stock" value of 1 to appear, and calculate a minimum stock value based on the sold quantities of last six months, using a formula. (Let's say, for the sake of simplicity, half that value).

What would be the best strategy to use? I'm more or less comfortable with Power Query, but not sure how to build up tSS.
Attached a basic workbook with a (fictional) example tProducts.

[#2]

@bartvana 

You may use technique described here Self Referencing Tables in Power Query - Excelerator BI to add custom column with 1/0 to tProducts table (or to reference on it).

[#3]

I have the impression this won't work for me. The original data I use to make tProducts doesn't have an ID column. I can add one of course in Power Query. But if the list of products in the ERP system changes (where I get tProducts from), then the ID's I add in Power Query will reference other products. (I don't know if I make myself clear? Imagine the second product in the ERP system disappears. Power Query adds an ID column. ID5 will now reference the product that previously was ID6).

[#4]

@bartvana 

Yes, you need to have unique ID (which could be product name itself) for that. But you need to have it in any case, otherwise how to recognize the products in the list which have no id, same names but perhaps only some other parameters are different? If 1/0 is related only to product name and nothing else, you may create another table with unique product names and use it.

[#5]

@Sergei Baklan The product names are unique and can serve as ID. But they're not "mathematical" and the list changes regularly (additions and deletions).
Taking this into account, how would you solve this in my simple example workbook?

So in sheet2 I need the list of products where I can manually add the SS value (0 or 1). But let's say prod02 is deleted in sheet1 and prod15 is added, how can sheet2 update with the new list of products without losing the SS values already assigned to still existing products?

[#6 · Accepted solution]

@bartvana 

Please check in attached. I trim and lowcase product names to use them as ID, but if you data is clean it's not necessary.