Dealing with non-matches in PowerPivot relationship

[cr731]

I've created some relationships between tables in PowerPivot and occasionally I will have values that are un-matched. I want all of these non-matches bucketed into a single category, but by default these get labeled (blank) in a Pivot Table. Is there a way to replace that (blank) with a different value? If doing this in normal Excel, I would do something like,

=IFERROR(VLOOKUP(...),"Value to replace (blank)")

 

[Matt Allington]

Blank indicates a missing primary key - Ie the absence of a matching record. Therefore there is no way to fix that with a formula.

What you should do to fix this is to find the missing values in your source data and add them to the lookup table. You can do this with Power Query or write suitable queries in SQL or what ever the source is.

 

[cr731]

The thing is, I would like to only have to specify 2 values and then group the rest into an 'Other' category without having to specify all of those values. For instance, say my column contains values such as,

- Item 1
- Item 2
- Item 3
- Item 4

I want Item 1 to be categorized as 'Category 1' and Item 2 to be categorized as 'Category 2,' but then Items 3 and 4 to just be grouped into Other. My list contains hundreds of values, and I only need to call out 2 items from that list, with everything else being Other. Creating a relationship can do this but then it names Other as blank. Maybe there is a better way but I'm not sure what it is.

 

[Matt Allington]

If you want something other than blank, then the first step is to ensure you have a match in the lookup table. No match = blank (no exceptions). So first make sure there is a match. Then you could create a calculated column that does what you want. Your column could be like this

=SWITCH(LookupTable[souce column name],"Item 1","Category 1","Item 2","Category 2","Other")