Power Pivot Principles: Introducing the CONCATENATE Function

Welcome back to our Power Pivot blog.  Today, we introduce the CONCATENATE function.

The CONCATENATE function takes two text strings and joins them into one text string.  It uses the following syntax to operate:

Simple? 

Let’s move on to an example.  Assuming we have the following Table with product descriptions split up into three columns arranged in a “realistic” but poor way, viz.

We want to combine the data into a product description column of some sort, such as

Using the CONCATENATE function in Power Pivot:

we obtain

With that example, the limitations of the CONCATENATE function becomes painfully apparent, it can only accept two text inputs at once!

Imagine the nightmare nested CONCATENATE formula we would have to create if we wanted to include spaces “ ” in between each column.

Luckily, there is an alternative. We can use the ‘&’ operator, it is the equivalent text concatenation operator.

Using the ‘&’ operator, we can concatenate the three columns like this, also including the spaces in between!

Of course, if you really wanted you could combine the ‘&’ operator into the CONCATENATE function:

But we probably wouldn’t… the ‘&’ operator is just much easier to use, and simpler for other users to understand.

That’s it for this week, tune in next week for more Power Pivot! Until then, 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.