Power Pivot Principles: Grouping Text Columns with New Categories

Welcome back to our Power Pivot blog. Today, we will use the SEARCH and SWITCH functions to create custom columns that group text columns into new categories.

Last week, we covered the SEARCH function and we used it to identify text strings within a column, you can read more about it here. This week, we are going to combine the SEARCH function with the SWITCH function to demonstrate a potential use case. You can read more about the SWITCH function here.

Let’s look at a similar data table to the one used last week:

Our task now is to create a new column that works as a new subcategory for each of the products.  For example, if we want the new column to classify Business Shoes as Footwear, we would use the following IF function:

This creates the following calculated column as follows:

If we wanted to include Casual Slippers, we would have to use a nested IF statement:

But what if we wanted to reclassify Gloves and Belts as Clothing?  It would result in a potentially nasty nested IF statement!  As we hinted earlier in this blog, we can use the SWITCH function together with the SEARCH function instead:

In this case, we have to use TRUE as the expression so that as the function cycles through each row the expression will be ‘true’, so it will perform the subsequent searches for each row. I f we do not use the TRUE function as the expression, we will get the following error message:

That’s it for this week, until next time, happy pivoting!

Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.