Clean and prep your data

- [Instructor] So let's go ahead and connect to some data. In this example we're gonna use an Excel sheet that we can find in our Exercise folder, called GlobalVehicleSales. Now when we look at the data window here, we notice that Tableau's identified that it could do a cleanup job for us. On the left-hand side, it offers some help. So we see that Tableau has identified some problems with this Excel file. Let's just take a quick look at it to see what's going on. So this is the sheet we just connected to, and what you can see is there's the data table in the center, but also there's another text right in there.

We've got the title, we've got some information to the reader, and we've got some formatting going on. Now this makes it really readable for somebody reading this table in Excel. But in terms of data, we've got all these extra fields that Tableu's gonna interpret as different things. So for example, when we go back and look in Tableau, we see that this first column here, instead of picking up the name of the column such as Region and Countries and then the years, it's actually picking up F1, F2, F3, and in the center there New PC Registration. Now, this isn't what we want.

Tableau's data interpreter understands that Excel documents can also have extra data to make them more readable. If we click this little button over here, for Clean with Data, it removes all this extra information. We can review the results by clicking on here. We get some information about what Tableau has done, and we can see, highlighted here, the things it's changed. So it's identified that the green sections are data, the orange sections are headers, and everything else it ignored.

In this example, column O also has that information for us. Now if we go back into Tableau and go to our sheet, we see that, instead of having the dimensions and measures with their original names of F1, F2, etc., we now have the regions, countries, and the measure names as they should be. We can now go ahead and create a visualization. We can go one step further and clean this up more by changing the case of the countries.

We can do this using a calculated field. To do that, we're gonna right-click on our dimension and go to Create Calculated Field. So we'll give the result a name, in this case we'll call it Country, and now we need a formula, to change the case into leading case. Let's do that, we're gonna have to copy and paste it in a formula, which you can find in your exercise file. Tableau tells that the calculation's valid, so we can click OK, and our new Country has been created.

Just drag that into the view. Well, we see now that we just got capital case for all of our countries.