How to combine string fields in Google Data Studio

How to combine string fields in Google Data Studio

Have you ever needed to combine string fields in Google Data Studio? I tried to do it the other day and it was more difficult than expected.

In this post I'll show a method that solves how to combine different string fields into one field. This method is from Harry Bryars whom you can follow on Twitter or Medium. He was kind enough to show me this method!

Looking at the hypothetical Data Set

So in this hypothetical data set we have a list of tourist names in Column A and then the country they visited in each year, listed as five seperate columns. You'll notice that many cells are blank as some tourists did not travel to countries in some of the years. or in some cases no countries.

The First attempt at Combining the strings

So the first attempt at combining the strings seems like an obvious solution.

Use the CONCAT function to combine all the strings together, as shown below.

Here is the actual CONACT formula if you want to try it yourself.

CONCAT(Country 2015,", ", Country 2016,", ", Country 2017,", ", Country 2018,", ", Country 2019)

And below is what the formula displays in our Data Studio report. You'll notice that if all the fields (columns) contain a country then the formula works as intended and combines the countries into a list.

However if one of the columns has no county listed and is null then the formula doesn't work.

Second attempt at combining the strings

This is the second version for the formula from Harry Bryars that worked!

So it uses multiple CASE statements to insert a "_" when the column value is null, and otherwise lists the country.

Below is how the formula show look.

Here is the actual formula if you'd like to try replicate this with your own data source.

CONCAT
(CASE WHEN Country 2015 IS NULL THEN "_" ELSE Country 2015 END,", ", 
CASE WHEN Country 2016 IS NULL THEN "_" ELSE Country 2016 END,", ", 
CASE WHEN Country 2017 IS NULL THEN "_" ELSE Country 2017 END,", ", 
CASE WHEN Country 2018 IS NULL THEN "_" ELSE Country 2018 END,", ",
CASE WHEN Country 2019 IS NULL THEN "_" ELSE Country 2019 END,", "
)

Below in the final column you can see that we have the formula working close to how we would like it to.

Below is the actual Google Data Studio report if you'd like to take a look.



My Other Blog posts

If you enjoyed this post then you might like my other blog posts.


Combine Data from Google Analytics and Wikipedia in Google Data Studio

Combine Data from Google Analytics and Wikipedia in Google Data Studio

In this blog post I'll give an example of how you can combine Data from Google Analytics and Wikipedia in Google Data Studio using Google Sheets.

Read more


Use Google Sheets to pull data from Wikipedia to Google Data Studio

Use Google Sheets to pull data from Wikipedia into Google Data Studio

So in this post I'll show you how to use Google Sheets to pull data from Wikipedia to use in Google Data Studio.

Read more


Compare the Average Monthly Temperatures of Cities in Google Data Studio

Compare the Average Monthly Temperatures of Cities in Google Data Studio

In this post I'll share a Data Studio report I made that lets you Compare the Average Monthly Temperatures of Cities around the world.

Read more


How to make a Currency Exchange Rate World Map in Google Data Studio

How to make a Currency Exchange Rate World Map in Google Data Studio

So in this blog post I'll show you how to make a currency exchange rate world map in Google Data Studio using an interactive Google Map..

Read more


How to build a custom Google Map in Data Studio using Google Sheets and Geocode

How to build a custom Google Map in Data Studio using Google Sheets and Geocode

In this post I'll show a way of creating a detailed, custom Google Map in Google Data Studio.

So let's say you have a list of Addresses that you'd like to map out in Data Studio. What are the steps to get this done? I'll go over how you can do this using Google Sheets, the Geocode add-on and the newly released Google Maps chart type in Data Studio.

Read more


Show Personality Quiz results using a Radar Chart in Google Data Studio

Show Personality Quiz results using a Radar Chart in Google Data Studio

In this post I'll show you how to Show Personality Quiz results using a Radar Chart in Google Data Studio.

Read more


How to make an Interactive List of People in Google Data Studio

How to make an Interactive List of People in Google Data Studio

So in this post I'll show you how to make what I've called an Interactive List of People in Google Data Studio.

Read more


How to Make an Interactive Map in Google Data Studio

How to Make an Interactive Map in Google Data Studio

So in this post I'll show you how to create what I'll call an Interactive Map in Google Data Studio. This map will cities you can click on to filter the images displayed.

Read more


How to Filter by Email in Google Data Studio

How to Filter by Email in Google Data Studio

In this post I'll be showing you how you can filter by email in Google Data Studio.

This is a new feature, and I'll be giving a simple overview of it and how it works. To learn more about this feature see the official Google notes or this blog post from Wissi Analytics.

Read more