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"
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. :)
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,{"ProductID"},"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.html?Is=Website 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/