Items not found

[MarkCBB]

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. 

[ImkeF]

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.

[MarkCBB]

@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

[ImkeF]

Hi Mark,

the information of how your results should look like is missing.

[MarkCBB]

post edited 

[ImkeF · Accepted solution]

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,{"Product​ID"},"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"

[MarkCBB]

@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. 🙂

[ImkeF]

1) I started in the UI with a Group By on StoreID, selecting "All Rows" in "Operation" instead of the default and chaning "Count" to "Partition" under "New Column Name". This returns the following string:

   #" Grouped Rows" = Table.Group(Sales, {"StoreID"}, {{"Partition", each _, type table)}}),

Afterwards I've renamed that step to "Partition" as well and nested the Join-operation around the "_", which stands for the partitions.

Partition = Table.Group(Sales, {"StoreID"}, {{"Partition", each Table.NestedJoin(_,{"ProductID"},Product,{"Product​​ID"},"NewColumn",JoinKind.RightAnti)}}),

In order not to have to write the full code manually, I simply performed a Merge-operation using the UI, copied the code and adjusted the parts who needed to be adjusted 🙂 Afterwards simply deleted that step.

These techniques can look a little strange if you're not accustomed to functional languages. I strongly recommend to watch this webinar:  https://info.microsoft.com/CO-PowerBI-WBNR-FY16-06Jun-21-Amazing-Things-with-Power-BI-Registration.h... The best hour you can invest in building you M-skills!

Q2: I did that using pure UI: click the triangle in the column header -> Number Filters -> Does not equal -> select null.

But the step was only necessary with my sample data because there were stores in it who didn't have any missing products. So this step wouldn't work with your data and is obsolete, as long as you don't have any store with missing products.

Q3: This is sample data standing for your tables. The idea was to deliver a query that can run on its own, giving you the chance to follow along every single step and see how the query results evolve. You'd have to copy all this code and paste it into the advanced editor. But don't use Internet Explorer for it but a different browser, because it will inject non-printable characters that will destroy the code and raise error-messages ("ProductID not found" - because there is a break between Product & ID).

You'll find some more information for the techniques I've used under these links:

https://blog.crossjoin.co.uk/2016/06/03/creating-tables-in-power-bipower-query-m-code-using-table/

https://blog.crossjoin.co.uk/2015/05/11/nested-calculations-in-power-query/

And even more learning resources for M here: http://www.thebiccountant.com/learning-resources/

[MarkCBB]

@ImkeF Thank you so much for the above, I am going to go through all the content and will get back to you. Thank you once again.

[ImkeF]

@MarkCBB : I've edited my last response because there is a bug with code copying when you use Internet Explorer: https://ideas.powerbi.com/forums/360879-issues/suggestions/15011475-code-editor-in-forum-injects-non...

Please vote for this being fixed!!

Thank you.

[MarkCBB]

Hello @ImkeF

Thank you for the Sharing that Video, it was very helpful. 

After watching the video I realised that DAX might be a better approach to the items not found. Currently this is taking well over 4 hours to run - But I have no idea where to start. (I upgraded my RAM to 64G hoping that that would solve the 4 hour wait but it didnt really make an impact).

At the moment my machine is just not coping with the Query, and the more data i am adding to it the slower this is going?

Can something like this be done in DAX, or do I need to change the date range of the Sales table to be only X months insteal of all months. From a business few 3 months would be the min.

[ImkeF]

Hi Mark,

if you have a performance issue here, you should shrink your SALES-table to distinct values on Product ID only and buffer the result. Then move on with the same technique from there.