From the course: Power BI Data Methods (2019)

Cleaning text fields

From the course: Power BI Data Methods (2019)

Start my 1-month free trial

Cleaning text fields

“

- [Instructor] In the NOAA station list Power BI file file we set up earlier, we already split the data into separate columns and renamed some of them. Now we can go into the columns and make changes to make them cleaner and also use in later transformations. In Station ID, when we click into a value in the column, we see that there are spaces after the Station ID. This is because when we split the positions by a fixed number of spaces, it leaves extra spaces between the fields rather than trimming it to the exact length. Selecting the Station ID column, we right-click, go down to the Transform function where we can see that we can Trim, Clean, Capitalize Each Word, we have a number of options to transform this column. For the text field, when we select Clean, it removes any non-printing characters. Let's go ahead and do that to make sure we don't have any in the Station ID field. Now lets remove the trailing spaces by again right-clicking on the Station ID column name, selecting Transform again and choosing to Trim. We see the applied step for trimming the text. Now let's go back into the same value and we see that we have eliminated the trailing spaces. Now lets go into the Station Name and we're going to remove the trailing or leading spaces for this column as well. And we're also going to capitalize each letter. Right now we see everything in capital letters and capitalizing each word makes it a bit easier to read. Again, we right click on the column name, Transform and select to Capitalize Each Word. There we have it. Some simple text transformation steps that can work wonders for our data and help make the table easier to read and work better in transformation and other steps later.

Contents