From the course: Learning Data Analytics: 1 Foundations

Changing case and replace values

From the course: Learning Data Analytics: 1 Foundations

Changing case and replace values

“

- [Instructor] We change case and data for a million reasons. For example, we might need to uppercase two letter abbreviations for state, or maybe names have been entered and sometimes they're uppercase and sometimes they're lower case and we want to convert them to proper case. We're usually doing this for consistency across our data. It's also important to note that Power Query is case sensitive. So if we're going to use tools like replace values sometimes to make it simple it's easier to convert to a different case. In our example here, we want to actually uppercase first name and last name. So let's go take a look at our data. I'm going to right click my sales order header and I'll choose edit. It is easy to convert this to uppercase. I can right click first name. I can get a transform. I see options for lower, upper and capitalize each word which the function equivalent of that is equal proper. So I'll go ahead and do upper case. If I know that I want to do multiple fields, I can select them and do uppercase. So I'll go ahead and highlight last name. I'll use my control key and highlight city, state, and postal. I can right click, go to transform and choose upper case. Okay, my sales order number. So it's stored with the SO in my data or how I receive it is with the SO, but for reporting purposes it would be better if this said sales order number and then it had the five digit number. So what I'm going to do is I'm going to use replace values. There are two ways to replace values. I can choose a value and right click and replace or I can choose the column and choose replaced values. I'll choose replace values, I'll type SO. And I want to replace that with sales order number. And then I'll go ahead and put a space after that. This is something that people do with find and replace all the time. If I go to my advanced options I can choose additional options like match entire cell contents or replace using special characters. Here, I just want to find every occurrence of SO and replace with sales order number. All right, I'll go ahead and click okay. And now I see my properly set sales order number. I've decided I don't want those extra spaces in there. So I'm going to go to my gear shape and I'm going to change it to read sales order space number and then I'll add a colon there. See if I like that better. Perfect, okay. Let's go see those changes in our spreadsheet. I'll do close and load. Let's see my uppercase values. If I scroll over, I see my sales order number. There are different scenarios for why we change case replace values but you see exactly how simple it is. Just imagine if the only way to do this was by building functions or doing a manual find and replace, we would have a lot of additional columns and we would be touching that data a lot. And again, every time we manually touch it we degrade its quality level and just for a few letters. So even though there are different scenarios for why we change case replace values I definitely make the case for Power Query.

Contents