Remove empty Power Query columns using M script

This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Remove empty Power Query columns using M script
Remove empty Power Query columns using M script

Posted by Sam Lowrie on 01 August 2025

In this blog

Removing empty columns using M script
Returning a list of column names
Filtering a list
Filtering to only non null column names
Returning only columns in the list

Removing empty columns using M script

In Power Query, at the end of creating a long query, you may be left with a bunch of columns which no longer hold anything useful.

Table containing a bunch of empty columns

My sample dataset contains 2 rows. The Sale 3 and Sale 5 columns contain no values.

It would be nice to be able to remove all the empty columns at once, rather than one-by-one. Doing this is surprisingly involved and requires multiple different functions which I have listed in this table:

Function	Syntax	Description
Table.ColumnNames	Table.ColumnNames(table)	Returns a list of column names from the given table.
Table.Column	Table.Column(table, columnName)	Select a single column out of the given table and returns the contents.
Table.SelectColumns	Table.SelectColumns(table, columnNames)	Use a list to define which columns to keep within the given table.
List.Select	List.Select(list, selectionFunction)	Filters a list by applying a function to each item and keeping those that match.
List.NonNullCount	List.NonNullCount(list)	Counts how many non null values are in a given list.

Returning a list of column names

To begin, select the last step of your existing query and add a new step by clicking the fx icon to the left of the formula bar.

This creates a new step that references the previous one, in this case #"Reordered Columns".

To return a list of column names we can use the Table.ColumnNames function, passing in the name of the step variable which returns our existing table.

Return a list of all the column names in the table

Now is a great time to apply sorting to these column names.

This creates a new step called Custom1 in the Applied Steps list on the right hand side of the screen.

This has created an applied step called Custom1

We will reference the name of this step later.

Filtering a list

To filter this list of columns we can use the List.Select function. Here's the syntax of the function:

List.Select( List to filter, Function to apply filter )

The second argument of the List.Select function accepts a user-defined function to apply a filter to each row of a list. The basic syntax of a user-defined function to do this is:

(Parameter names) => Filter expression

To begin with, we'll use a placeholder filter which simply checks if 1 is greater than 0. This will initially return every column name from the list. Here's the code for our example:

For each list item, check if 1 is greater than 0 which it hopefully is!

Filtering to only non null column names

Now to create the actual filter we want!

To determine if a column is empty we can use List.NonNullCount to count the non null values and Table.Column to specify the column whose values we want to count.

Return the number of non nulls in a column

Counting the non null values in the CustomerId column will return 2 as there are two values in this column.

We can use this expression to replace the placeholder value 1 in our previous step. In the example below, for each column we are testing if the CustomerId column has more than 0 non null values.

Test for each row if the same condition is met

Unsurprisingly if you ask the exact same question for each list item, they all return the same answer.

To count the non null values in each column we replace the hardcoded "CustomerId" column name with the name of the col parameter of the user-defined function.

Two of the columns had nulls and now have been removed

The two columns that only contained nulls have been removed.

Returning only columns in the list

You could modify this query step further, but it’s cleaner to add a new step by clicking the fx button again.

Adding a new clean step with less going on

Custom1 is the name of the step which produces the list of non-null column names.

You can use the Table.SelectColumns function to return a selection of columns from a table. Here's the syntax of the function:

Table.SelectColumns( Table, List of column names )

As a test, use this function on the original table #"Reordered Columns" and pass in a list of specific column names as shown below:

Specify which columns to return from a table

This is especially useful when certain column names need to be preserved.

Finally, replace the hardcoded column names with the variable Custom1.

What a journey but finally the nulls are gone!

It’s been a bit of a journey, but now your query can dynamically remove all empty columns!

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

ALL BLOGS

POWER BI BLOGS

POWER BI BLOGS

Removing empty columns using M script

Returning a list of column names

Filtering a list

Filtering to only non null column names

Returning only columns in the list

Head office

London

Manchester