ICAEW.com works better with JavaScript enabled.

Excel Tip of the Week

Excel Tip of the Week #389 - Power Query: Dropping rows and columns

Author: David Lyford-Smith

Published: 13 Apr 2021

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello all and welcome back to the Excel Tip of the Week! This week, we have a Creator level post in which we’re looking at how to remove rows and/or columns from data imported into Power Query – either manually or automatically.

We have several other posts about Power Query archived in the Tip of the Week Index, so if you want to learn more, check those out.

Dropping rows from your data

Let’s take a look at some sample data:

Image 1

We only want to include customers A and C in our summary data. To do this we simply apply a filter on the Customer column using the dropdown arrow; so long as this filter is still active when we complete our other transformation steps and load the query, then the other customers will be removed. In this case we filter out the other customers and then group by customer:

Image 2

We can also remove rows using automatic operations instead of directly choosing which to include / exclude. Let’s take this table as a second example:

Image 3

This table has a mixed format – the columns aren’t used consistently, with the customer totals appearing in the VAT column, as well as an unnecessary grand total row at the bottom. We can clean this up using Home => Remove Rows:

Image 4

There are several useful options here. For us, we want to remove alternate rows (to clear out the gaps and subtotals, then remove the bottom row (to clear out the grand total row). With that all done, we can then export our sanitised data back into Excel:

Image 5

As you can see, there are also easy options for removing duplicate rows, rows with errors, blank rows, and so on. These tools can accomplish most common data cleansing tasks.

Removing columns

If instead we want to remove columns, our options are more limited. Let’s look at this example:

Image 6

If we want to remove columns manually, we can select the ones we want and right click or use the Home menu to either remove those columns, or remove all the other columns. But this lacks the ease and fluidity of a filter, which could act more flexibly, and as-is there is no way to automatically remove e.g. alternating column or the first/last columns as there is with rows. At first things may appear hopeless – but there’s a clever trick!

On the Transform menu, we have the Transpose option. It swaps the rows and columns of the table:

Image 7

Note that the first column is now the first row, rather than the header row – we could change this using the Transform => Use first row as headers option if we wanted (or indeed do the reverse and move the headers into the first row of data). In this case we will do that to demonstrate how to keep only the most recent exchange rates, for example if this is connected to an automatically growing dataset. We can then re-transpose to restore the original orientation and export:

Image 8

The full process involves demoting and promoting headers multiple times – to follow the full query, check it (and our other examples) out in this accompanying workbook.

Excel community

This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.

Excel polaroid