Looker Studio – Tricks on how to use the REPLACE formula

Hey, everyone. Ryan Bowse from SchoolBI here. With today’s tips and tricks, we’re gonna go real simple and do something that will often be used by anybody building a report or dashboard, and that is the replace formula within looker. So given this a one star out of five and the complexity something you’ll probably use right out of the gate, and it can be used as many times and as often as you’d like. So if you’re not familiar with the replace feature One of the ways to use the replace feature is in the following way. Oftentimes, when you’re building a graph or you’re building a chart, the name of the label can be way too long. You might need to abbreviate it just to make things fit.

An example I’m gonna show in a second is that the city I’m using is very long, and I wanna shorten it. So you can use a replace feature formula in looker to replace that long name with a shorter name. So it’s that simple. I’m gonna show you exactly how to do it in real time. So one of the things you’ll notice here in my fake school Richmond Academy, I use this one all the time. You might be getting tired of me.

Saying this, but this is actually fictitious data, but it’s flowing from a source SIS system down into school BI into this dashboard. I have a graph up here that shows the distribution of families in which cities they, live in or reside in. And you’ll notice it says San Francisco Mill Valley, San Belling Game Tiburon. Now the one I’m gonna use is I’m gonna say, Hey, maybe that name San Francisco is way too long and I want it to be shorter. I just want it to be SF.

So what you can do is you can actually create a new field and I did that right here called and I’ll show you what I need it. I created a new field and I named it San Francisco replace calculated. If you follow along, this might be repetitive, but I always put the word calculated at the end because it easier for me to know which ones I’ve touched and which ones I’ve created versus being from the source data itself.

So a replace statement or a formula is really easy. After naming it, I just typed the word replace and then I use the field that I’m gonna replace. So this is the city field. I’m gonna say replace city where San Francisco, the word San Francisco, and change it to SF.

It’s that easy. I could say SF Bay Area just to show you what it looks like. And then when I do that, it’s gonna turn to green, say the formatting’s correct. If it’s not, it will give me the red X Mar and tell me what they think is wrong.

And I’ll hit update. Now when I hit update, you’ll see that this now field is actually created on the left hand side. I named it San Francisco replace calculated. I probably would name it something a little short.

Now my graph or my chart is still using the original city field for this. So I’m gonna make sure that this graph is selected. And when I do that, I’m gonna then see on the right hand side the dimension that it’s measuring. And instead of using Citi, I’m gonna use this new field that I created.

And watch what happens.

That, value will just be replaced And ultimately none of the other cities will be touched because I didn’t ask it to change anything in that. I just said change it for San Francisco equals SF Bay.

This is a great simple way that if you have a lot or a name that’s long, it could be around a course and you’ve got some long name and you just wanna shorten it to make things a little cleaner. The replace feature is great. That’s it. If you have any questions, let us know. I’d love to hear how you’re using looker at your school and talk to you soon.