Forum Discussion
Items not found
Hi there,
I have 3 tables:
Product (ProductID, Product Name)
Stores (StoreID, Sotre Name)
Sales (Fact Table)
I need to create a list that contains Products that are not in a store, the fact table has the following columns:
Date
Sales
StoreID
ProductID
So I am trying to create a list of products that are not in a Store based on the sales. i.e. a store should have 50 products, but some stores only carry 40, I need to know what the 10 missing products are.
It does the right thing, just that you need to connect the code to your tables. Either like this:
let Sales = SALES, Product = P, Store = S, Partition = Table.Group(Sales, {"StoreID"}, {{"Partition", each Table.NestedJoin(_,{"ProductID"},Product,{"ProductID"},"NewColumn",JoinKind.RightAnti)}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"NewColumn"}, {"NewColumn"}), #"Expanded NewColumn" = Table.ExpandTableColumn(#"Expanded Partition", "NewColumn", {"ProductID"}, {"ProductID"}), #"Filtered Rows" = Table.SelectRows(#"Expanded NewColumn", each ([ProductID] <> null)) in #"Filtered Rows"or directly:
let Partition = Table.Group(SALES, {"StoreID"}, {{"Partition", each Table.NestedJoin(_,{"ProductID"},P,{"ProductID"},"NewColumn",JoinKind.RightAnti)}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"NewColumn"}, {"NewColumn"}), #"Expanded NewColumn" = Table.ExpandTableColumn(#"Expanded Partition", "NewColumn", {"ProductID"}, {"ProductID"}), #"Filtered Rows" = Table.SelectRows(#"Expanded NewColumn", each ([ProductID] <> null)) in #"Filtered Rows"
11 Replies
If you merge your transaction table (which also holds your stock level?) with the stores on JoinKind.RightAnti, only the missing ones will be shown. Perform this on a partitioned transaction table on StoreID so it will be fast & you don't have to write additional filters:
let Sales = #table({"StoreID", "ProductID", "Stock"}, {{1, "A", 10}, {1, "B", 20}, {2, "A", 100}, {3, "A", 20}, {3, "B", 10}, {4, "B", 10}}), Product = #table({"ProductID"}, {{"A"}, {"B"}}), Store = #table({"StoreID"}, {{1}, {2}, {3}, {4}}), Partition = Table.Group(Sales, {"StoreID"}, {{"Partition", each Table.NestedJoin(_,{"ProductID"},Product,{"ProductID"},"NewColumn",JoinKind.RightAnti)}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"NewColumn"}, {"NewColumn"}), #"Expanded NewColumn" = Table.ExpandTableColumn(#"Expanded Partition", "NewColumn", {"ProductID"}, {"ProductID"}), #"Filtered Rows" = Table.SelectRows(#"Expanded NewColumn", each ([ProductID] <> null)) in #"Filtered Rows"Just paste this code into the advanced editor if you want to follow along the single steps.
ImkeF Thank you for the below, however it does not look like this is doing the right thing, let me explain:
Below are example table I created:
Now, If you have a look at this table, and we use StoreID 10 as an example. The store has sold the following produts, 2,3,6,7,8,9. Which means that it has not sold the following Products, 1,4,5.
I would like a list of those products that are missing, (i.e. 1,4,5) so the result for Store ID 10 would be:
the other reason I need to do this is so that I can show the % of Products are are ranged/Carried by a store. so StoreID 10 would have 66.6% of the full range as they are missing 3 of the 9 products. This would then need to roll up into an other all result. i.e. an average of 72% of the Range is carried by all the retail stores.
Below are download link to the data as well as the PBIX files if this helps in anyway:
https://www.dropbox.com/s/k8p1ueewvch68c1/ANIB%20Test.pbix?dl=0
https://www.dropbox.com/s/g6apce863011vxs/ANIB%20Testing.xlsx?dl=0
We call this report an ANIB (Articles Not in Braches). We use it to ensure that all Stores carry the full range of Products.
I hope this explination makes sence
Hi Mark,
the information of how your results should look like is missing.
