2

With PowerPivot, how do I find items only in one category? Here's an example:

item category number obera f6 7 artois f7 2 obera f7 3 ella f6 9

If I choose f6, the output for this example would be:

ella

(it would not contain obera since that also appears in the f7 category). Thanks in advance.

Edit: what I want to do is for each category (of which I have a lot), display items that only appear under that category and nowhere else.

PonyEars
  • 957

1 Answers1

1

I built a solution to this using the Power Query Add-In. You can download it from my OneDrive - it's the file Power Query demo - items that are only in one category

http://1drv.ms/1AzPAZp

Here's a description of how I built it:

Using Power Query, I created a Query ("Table1") from the Excel table. I unchecked the option to Load to worksheet - this is just for input to the next Query.

I then created another Query ("Table1 multi-category") from the same Excel table. In that Query I Merged the "Table1" Query, joining on Item. Then I expanded the Item and Category columns from "Table1". Note that at this point I have increased the number of rows, covering all combinations of Item.

Then I added a Custom Column "multi-category". The formula is just:

[category] <> [NewColumn.category]

I already joined on Item, so this tests whether there are multiple categories for this item. Then I filtered on this column, just keeping the TRUE values.

I then added a Group By to group by Item and Category, and get the Max value of the "multi-category" column - this caters to the scenario when an item has more than 2 categories.

Finally I removed up the "working out" columns, and again unchecked the option to Load to worksheet - this is just for input to the next Query.

Next I created another Query ("Table1 final"), again starting from the original Excel table. This time I Merged in the "Table1 multi-category" Query, joining on Item and Category, and including rows that don't match.

Then I expanded and renamed the "multi-category" column, and replaced null values with FALSE. This Query is delivered to a new table on a new sheet "Multi-category".

Finally I built a pivot table on that new table on the "Multi-category" sheet. The "multi-category" column tells you which Items exist in multiple categories.

It's obviously quite a few steps to get to this point. However each step in isolation is quite simple and fairly easy to understand, given the WYSIWYG Power Query editor. It's certainly a lot easier to follow than debugging VBA code and 100x better than trying to debug Excel formulas.

Mike Honey
  • 2,632