Deleting Columns and Demoting Headers

In this lesson were going to focus on removing unnecessary columns and moving the headers into the first line of the dataset.

Removing columns is useful, both to slim down a dataset and reduce a dataset as well as to really focus in on the information that is important in a file. Demoting headers can also be necessary if there’s transformation or cleaning of the text in the headers that we need to do, as the Headers can’t be transformed without specific functions. In the case of the file in the example, the headers are split by delimiter and will eventually need to be separated. We can only do this if the headers are part of the main dataset.

Ok once we’ve got into the PowerQuery Editor (from the standard Excel screen, select Data, then Queries and Connections and then in the Queries and Connections pane, right click on a query and select ‘Edit’, we need to get rid of some columns.

How to delete columns

In the example video, we remove the column on the left of the screen called Source.Name. This column shows the file in the folder that each row has come from. This can be quite useful, for example for introducing data creation dates into datasets. However for this lesson, we’re going to remove it.

There’s three main ways to remove a column:

We can either:

1. select the columns that we want to get rid of by selecting the unnecessary column (if we want to select more than one then press Control and select all the columns you want to remove), then right click on any of the highlighted column and select Remove Column from the pop up menu
2. With can also highlight multiple columns by selecting the column on the lefthand most column of the columns we want to remove, then pressing shift and then selecting the right most column. This highlights all the columns in between. We can then right click on any of the highlighted columns and select to remove columns.
3. We can also select Choose Columns in the Manage columns section of the ribbon in the Home tab and then we can choose the columns that we want to keep using the drop down menu.
4. Finally we can just select the columns that we want to keep on the preview screen and then right click a title and select Remove Other Columns (or select the columns we’d like to keep using the Remove Other Columns drop down option on the Home tab ribbon.) This can be a really useful option to only ever keep the columns we want in the dataset, no matter which columns are added to a dataset over time.

Demoting Headers:

At the moment in our video, our data is split by a space, a comma and then another space; a slightly elaborate delimiter.

Unfortunately, this also applies to our top line which has been promoted into the Headers. This line therefore needs the same cleaning and formatting work that we apply to the remainder of our dataset. To do that cleaning and formatting then we need to demote those headers into the first row

To demote headers then:

1. Go to the Transform tab
2. Select the drop down menu in Use First Rows as Headers. This then gives us the option to Use headers as First Row,
3. This brings up a new action in our Query settings pane of Demoted Headers.