# Power Pivot Principles : Selecting from Multiple Lookup Values

12 November 2019

*Welcome back to the Power Pivot Principles blog. This week, we are going to introduce a method to extract a single value from the group text columns.*

In our previous blog, we introduced a method to group text columns into new categories. For example, consider the situation below. We have data tables containing **Product Type** and the **Lookup** categories. We can create a column that will highlight if any one of the values from the **Lookup** table is present in the **Product Type** column. For instance, if the **Product Type** value is ‘Leather Belt’, we would want the column to recognise that that value had ‘Belt’ in it.

However, in some cases, multiple values would be captured by the formula and it is hard to decide which category should be given to a specific type. For example, if the product type is ‘Business Shoes’, the result would return ‘BusinessShoe’, as shown below:

Both categories **Business** and **Shoe** are listed in the lookup table, and the formula returns both values in the context. However, only one category should be applied to the **Product Type**: the value should be either **Business** or **Shoe** in this case. Therefore, we need to find a method to split the content if there are multiple categories.

After loading the **Product Type** table and **Lookup** table into Power Pivot, we create a calculated column **TypeFilter** with the formula below:

=CONCATENATEX(

'Lookup Table',

IF(

SEARCH(FIRSTNONBLANK('Lookup Table'[Lookup],1),'Product Type'[Product Type],,999) <> 999,

'Lookup Table'[Lookup]&",",

""

)

)

This code is very similar to the code we used in our previous blog, with one key difference. Instead of returning with the **'Lookup Table'[Lookup]**, we change it to **'Lookup Table'[Lookup]&”,”**. By adding the delimiter of a comma, we can split the content by using other text functions such as **MID**, **LEFT** and **RIGHT** *etc*.

If we take the first selection criteria as the main category, we may create a calculated column **ChooseFirstItem** and use the **LEFT** function to determine the context of first category, as shown below:

=LEFT(

'Product Type'[TypeFilter],

SEARCH(",",'Product Type'[TypeFilter],,999)-1

)

The result would be:

The **SEARCH** function returns the location of the comma delimiter and the **LEFT** function extracts the number of characters, as determined by the **SEARCH** function. If the second category should be considered as the main category, we need a few extra steps to extract the second context as required.

We can create a calculated column **CalculatedNoChar** to search the location of second delimiter, with the start position at the first delimiter, as shown below:

=SEARCH(",",

'Product Type'[TypeFilter],

SEARCH(",",

'Product Type'[TypeFilter],,0)+1,0

)

-SEARCH(",",

'Product Type'[TypeFilter],,0

)-1

The result would be:

Next, we can create a calculated column **LastItemCalculation** and use the **MID** function to find the second category, with the condition that the value of **CalculatedNoChar** must be greater than zero.

=IF('Product Type'[CalculatedNoChar]>0,

MID('Product Type'[TypeFilter],

SEARCH(",",'Product Type'[TypeFilter],,0)+1,

'Product Type'[CalculatedNoChar]),BLANK()

)

The result would be:

The last step is to create a calculated column **ChooseLastItem** . We can use the **IF** function to return the second category if the value in **LastItemCalculation** is not blank, as shown below:

=IF(ISBLANK('Product Type'[LastItemCalculation]),

'Product Type'[ChooseFirstItem],

'Product Type'[LastItemCalculation]

)

The result would be:

That’s it for this week!

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.