Recently I was working with a data source in Google Data Studio that needed a bit of tidying up. It was actually Google Analytics data, and the Event Action contained the html entities & and ' which needed to be replaced with the & and ' characters respectively.
I knew how to replace either one of those values using a calculated field:
REPLACE(Event Action,"&","&")
But how should I tackle the second replace? Well, one approach would be to create a second calculated field that uses my first calculated field as the input:
REPLACE(My Example Field,"'","'")
That works fine for two replaces, but what if I needed to make 20? Or 50? The more replaces I need, the less feasible it becomes to create and maintain a calculated field for each step.
The good news is that it is possible to nest REPLACE functions (and other functions) in Google Data Studio. So instead of the two-step process above, I could just use the following formula:
REPLACE(REPLACE(Event Action,"&","&"),"'","'")
So you can see that within the one formula, the output of the & REPLACE then becomes the input for the ' REPLACE.
This seems pretty straightforward, but I couldn't find it documented anywhere on Data Studio Help so I thought I would share.
Brilliant! Thanks James, you saved my day 🙂
Brilliant!
I used it to nest 6 levels and it works flawlessly.
Thanks James, you saved my day 🙂
Gonzo
Best Tip ever 🙂
Didn't get it in a rush. Your formula makes so much sense!
Alex
Perfect! Thank you!!
This is unreal, thank you so much!
Perfect, thanks a lot!
Is it possible to nest these two expressions together? I would like to remove all the hypens then capitalize the first letter of each word.
REPLACE(Campaign, "-", " " )
CONCAT(UPPER(REGEXP_EXTRACT(Campaign, "(^.)")) , REGEXP_REPLACE(Campaign, "^.", ""))
Hi Siobhan - yes, it's possible. If you want to remove the hyphens first, you should replace both the instances of 'Campaign' in the second expression with the first expression. This gives you something like:
CONCAT(UPPER(REGEXP_EXTRACT(REPLACE(Campaign, "-", " "), "(^.)")), REGEXP_REPLACE(REPLACE(Campaign, "-", " "), "^.", ""))
It might be easier to nest the other expressions the other way round, as you would only need to make one change rather than two. This would be something like:
REPLACE(CONCAT(UPPER(REGEXP_EXTRACT(Campaign, "(^.)")), REGEXP_REPLACE(Campaign, "^.", "")), "-", " ")
James, thank you! You're awesome!
Does anyone have any idea how I could do this?
SUBSTR(Lower(Full page URL, 21, 100))
I get this error
Incorrect number of arguments; expected three.
Also, while I'm on it. Can anyone help me with ability of renaming Page titles?
Right now I've edited the data source and added 25 replace items in a caluclated, but that seems to be the limit.