How to Remove Empty Columns in Excel with Power Query (Automated Method)

In this guide, I’ll show you how to auto­mat­i­cal­ly remove com­plete­ly emp­ty columns in your Excel data using Pow­er Query. This pow­er­ful tech­nique saves you from tedious man­u­al dele­tion and works even when columns con­tain null val­ues, emp­ty cells, and emp­ty text strings (rep­re­sent­ed by “”).

The approach I’ll demon­strate uses a spe­cif­ic Pow­er Query for­mu­la that iden­ti­fies and removes columns with no mean­ing­ful data, great­ly improv­ing your data clean­ing workflow.

Watch my detailed video walk­through of this process:

Understanding Empty Columns in Power Query

When work­ing with data in Excel, we often encounter columns that appear emp­ty but can actu­al­ly con­tain dif­fer­ent types of “empti­ness” that Pow­er Query rec­og­nizes dif­fer­ent­ly. It’s impor­tant to under­stand that Pow­er Query dis­tin­guish­es between null val­ues (tru­ly emp­ty cells), and cells con­tain­ing emp­ty text strings (“”).

An emp­ty text string occurs when a cell con­tains a for­mu­la that returns “” or when you paste such val­ues. These look emp­ty visu­al­ly in Excel, but Pow­er Query treats them dif­fer­ent­ly than cells with null val­ues. Our goal is to cre­ate a solu­tion that iden­ti­fies and removes columns regard­less of which type of “empti­ness” they con­tain, as long as the entire col­umn con­tains no mean­ing­ful data.

Importing Data into Power Query

To begin the process of remov­ing emp­ty columns, we first need to import our data into Pow­er Query. Here’s exam­ple how to do this:

1. Go to the Data tab in Excel
2. Click on “Get Data” → “From File” → “From Excel”
3. Locate and select your Excel file
4. Choose the work­sheet con­tain­ing your data (in my exam­ple, Sheet1)

After import­ing, you’ll notice that some columns might appear emp­ty but con­tain dif­fer­ent types of empti­ness — some cells might have null val­ues (shown as “null” in Pow­er Query), while oth­ers might be tru­ly emp­ty or con­tain emp­ty text strings. Our code will han­dle all these sce­nar­ios automatically.

The Power Query Solution

Rather than man­u­al­ly delet­ing emp­ty columns, we’ll use a cus­tom for­mu­la in Pow­er Query that auto­mat­i­cal­ly iden­ti­fies and removes all emp­ty columns. This approach is much more effi­cient, espe­cial­ly when work­ing with large datasets.

The Complete Code

Here’s the com­plete Pow­er Query for­mu­la that we’ll use to remove emp­ty columns:

= Table.RemoveColumns(#“Changed Type”, List.Select( Table.ColumnNames(#“Changed Type”), each List.Count( List.RemoveMatchingItems( Table.Column(#“Changed Type”, _), {null, “”} ) ) = 0 ) )

To use this code in your own projects, you’ll need to replace #“Change Type” with the name of your pre­vi­ous step in Pow­er Query. This ref­er­ence appears in three places in the for­mu­la, so be sure to update all instances.

Breaking Down the Formula

Let’s break down this for­mu­la to under­stand how it works:

The for­mu­la starts with Table.RemoveColumns(), which is the func­tion that will even­tu­al­ly remove our emp­ty columns from the table. This func­tion takes two para­me­ters: the table we’re work­ing with, and a list of col­umn names to remove.

To gen­er­ate the list of col­umn names to remove, we use List.Select(), which fil­ters a list based on a con­di­tion. We’re fil­ter­ing the list of all col­umn names (obtained using Table.ColumnNames()) to find those that meet our “emp­ty col­umn” criteria.

The core log­ic hap­pens in this part:

List.Count(List.RemoveMatchingItems(Table.Column(#“Change Type”, _), {“”, null})) = 0

For each col­umn, we:

1. Extract all val­ues in the col­umn using Table.Column()
2. Remove any val­ues that are emp­ty strings or null using List.RemoveMatchingItems()
3. Count how many items remain using List.Count()
4. Check if this count equals zero (mean­ing the col­umn is com­plete­ly empty)

If the count equals zero, the col­umn is con­sid­ered emp­ty and will be includ­ed in our list of columns to remove.

Implementing the Solution

To imple­ment this solu­tion in your Pow­er Query Editor:

1. Click “Add Step” to cre­ate a new trans­for­ma­tion step
2. In the for­mu­la bar, paste the com­plete code shown above
3. Replace “#“Change Type”” with the name of your pre­vi­ous step (typ­i­cal­ly the step after pro­mot­ing headers)
4. Press Enter to apply the transformation

You should now see your data with all emp­ty columns removed. The trans­for­ma­tion hap­pens imme­di­ate­ly, leav­ing only columns that con­tain at least one non-emp­ty value.

Testing the Solution

To ver­i­fy that our solu­tion works cor­rect­ly, we can test it with dif­fer­ent types of data. In my video, I demon­strat­ed this by:

1. Start­ing with a dataset that con­tained sev­er­al emp­ty columns (with var­i­ous types of emptiness)

2. Apply­ing our Pow­er Query solu­tion to remove these columns

3. Mod­i­fy­ing the source data to add a new col­umn with a sin­gle value

4. Refresh­ing the query to ver­i­fy that the col­umn with a val­ue is preserved

The solu­tion cor­rect­ly iden­ti­fied and retained columns that had at least one mean­ing­ful val­ue, while remov­ing columns that were com­plete­ly empty.

Advanced Usage and Adaptations

This solu­tion can be eas­i­ly adapt­ed to work with dif­fer­ent datasets. The key is to ensure you’re ref­er­enc­ing the cor­rect pre­vi­ous step in your Pow­er Query trans­for­ma­tion sequence. If your pre­vi­ous step is named dif­fer­ent­ly, sim­ply replace all instances of “#“Change Type”” with the appro­pri­ate step name.

When work­ing with very large datasets, this auto­mat­ed approach can save sig­nif­i­cant time com­pared to man­u­al­ly iden­ti­fy­ing and remov­ing emp­ty columns. Addi­tion­al­ly, the solu­tion is dynam­ic — if your source data changes and a pre­vi­ous­ly emp­ty col­umn now con­tains val­ues, refresh­ing the query will auto­mat­i­cal­ly retain that column.

Loading the Results

After apply­ing our trans­for­ma­tion, we can load the results back to Excel:

1. Click “Close & Load” (or “Close & Load To…” if you want to spec­i­fy a destination)
2. Choose your pre­ferred des­ti­na­tion (work­sheet, data mod­el, etc.)
3. The cleaned data, with emp­ty columns removed, will be loaded to your spec­i­fied location

The result­ing table will con­tain only mean­ing­ful columns that have at least one val­ue. This makes your data more con­cise and eas­i­er to work with.

When your source data changes, sim­ply refresh the query to apply the same trans­for­ma­tions to the updat­ed data. The emp­ty col­umn removal log­ic will be reap­plied auto­mat­i­cal­ly, adapt­ing to any changes in your source data’s structure.