Sign in or start a free trial to avail of this feature.
8. Formatting Columns
In this lesson, we will look at a variety of smaller tasks you might do with columns, including splitting columns, replacing values and removing columns.
- Splitting columns converts a single column into multiple columns
- Splitting by a delimiter creates a new column at every occurrence of the delimiter
- Replacing values replaces all occurrences of a particular value with a new value
- This is helpful if you want to make your data easier for people to understand
- If you have columns that are not of any use, you can remove them from the query
- This completely removes the column; hiding a column is not possible from the Query Editor
Over the last few lessons, our dataset has started to take shape.
In this lesson, we'll look at a few smaller, but still common tasks you might use to modify your columns in the Query Editor.
In an earlier lesson, we looked at merging columns.
Another common task is splitting one column into multiple columns. Let's demonstrate this with the date column. We'll select the date column, navigate to the Home tab, and select Split Columns.
We can split by delimiter or by number of characters.
Splitting by delimiter creates a new column each time a specific character occurs. Splitting by number of characters creates a new column after a specified length of the field.
We'll split by delimiter. The slash symbol has automatically been selected as a Delimiter, and we'll stick with that default. The Query Editor will now go through the date column and create a new column at every slash.
We'll press okay, and see that the date is split into three columns, representing month, day, and year. These columns have been assigned the names date one, date two, and date three. These are not very useful names, so we'll rename them.
We'll double click the column name for date one, and type in, "Month." We'll rename date two to day, and date three to year.
Next, we'll look at replacing values. Through your work, you may have the need to replace one value with another.
In this case, let's say we want to make a clear distinction between the day and month columns. We can do this by replacing the month number with a month name.
We'll select the month column, navigate to the transform tab, and select Replace Values.
Previously, we filtered the data to show only rows from the month of August. Therefore, we only need to make one change here, replacing eight with August.
This produces an error, and we're being told to enter a number.
Let's click cancel and close out this window.
If we look at the column headings, we can see that the month column has actually been classified as a number, and of course, August is not a number.
As such, we'll select the month column and change the data type from whole number to text.
We'll now select Replace Values again, and replace eight with the text August.
We'll press okay, and see that all values have been replaced. Finally, we'll look at the option to remove a column. Let's scroll over to the unique ID column.
This was useful in merging our original sales query with the quantity query, but we don't really need it anymore. We'll select the column, then navigate to the home tab and select Remove Columns.
This takes the column out of the query.
This function completely removes a column from the dataset, so you should only use it on columns you know you won't need later on.
Note that there is no option to hide the column in the Query Editor, however, you can perform this task in Power BI after loading the data.
This concludes our detailed look at formatting columns in the Query Editor.
In the next lesson, we'll continue with the subject of column manipulation by looking at how to add custom columns to the Query.