Sign in or start a free trial to avail of this feature.
11. Formatting Columns
In this lesson, we will look at more things you might do with columns, such as 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; do not confuse this with hiding columns in Excel
- Alt Y01, Y17 – Split column
- Alt Y02, Y09 – Replace Values
- Alt Y01, Y12 – Remove column
Over the last few lessons, we looked at how to merge different queries and how to use filtering options to focus on relevant data. In this lesson, we'll look at a few common tasks you might use to modify your columns in Power Query. In an earlier lesson, we saw how to merge columns. Another common task is splitting data from one column into multiple columns. Let's apply this functionality to the Date column. We'll select the Date column, and then navigate to the Home tab, and select Split Columns.
We can split by delimiter or by number of characters. In this case, we'll split by delimiter.
Power Query has automatically recognized the slash as a delimiter for data in this column. This means that we'll create a new column at every slash. We'll select OK and see that our data is now split into columns representing month, day, and year.
Notice that these columns have been assigned default names. These names aren't that useful, so we'll rename them. To do so, we'll simply double-click the column name and type a new one. As you might expect, we'll name the first column Month, and the next column Day, and the final column Year.
Next, we'll look at replacing values. There are many possible scenarios where you might want to replace one value with another. However in this case, a good example is our Month column. Since our month data is now in a separate column, we may want to replace the numeric values with month names to make it easier to read. We'll select the Month column, navigate to the Transform tab and select Replace Values.
You may remember that we previously filtered our data to show sales data from the month of August, so we only need to replace eight with August.
As we enter August, you're told to enter a number instead. If we click Cancel, we can see the Month column has actually been classified as a number, and of course, August is not a number. We'll select the Month column and change the data type to Text.
We'll go back to the Replace Values window and replace eight with August. We'll then select OK to make the change. Finally, we'll look at the option to remove a column. Let's scroll over to the Unique ID column. This was useful when we merged our original sales query with the quantity query but it's no longer essential. We'll right-click on the column name and click Remove to remove the column from the query. Be advised that you should only use this option on columns that you know you won't need later on. You may want to hide a column instead. Unfortunately, you cannot do this in Power Query. That being said, you can hide a column in Excel or Power Pivot after loading the data. This concludes our detailed look at formatting existing columns in Power Query. In the next lesson, we'll look at how to add new columns.