Jan 09 2021 12:37 AM
Jan 09 2021 12:37 AM
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.
Jan 09 2021 01:01 AM
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).
Jan 09 2021 02:10 AM
Jan 09 2021 02:18 AM
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.
Jan 09 2021 02:37 AM
@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?
Jan 09 2021 03:30 AMSolution
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.