Forum Discussion
Items not found
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"
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.
post edited :smileyhappy:
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"ImkeF Thank you, i have just checked and it works exactly as I wanted it to, thank you, I opted for the Direct approach.
I do have some questions, as I dont fully understand how you did this. I have read over the query a few times now.
Q1, The first line of the Query your wrote that starts with "Partition = "..... I dont se anthing like that in the UI, is this something that you "free handed"?
Q2, The Last line that starts with "Filtered Rows" - same question as above, did you use the UI or "Free hand" the line?
Q3, Wthin regards to you first query you sent to me, what is the purpose of these lines, could you explain it to me?
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}}),This is very helpfull, Thank you once again for assisting me with this. :)