Power Query: How to Filter Out Empty Rows and Columns with Custom M Functions

Have you ever opened a table in Power Query and found it filled with empty rows and columns? Talk about a mood killer! The good news is that you can quickly clean it up with a simple custom M function. Let’s explore the world of Power Query and learn how to eliminate those annoying empty rows and columns easily and effectively!

Understanding the Need

Before we dive into the code, let's discuss why it's important to remove empty rows and columns. Picture this: you’re analyzing a dataset that could reveal valuable insights. Every empty row or column creates a gap in your data, which can skew your analysis or lead to incorrect conclusions.

In fact, studies indicate that datasets containing more than 5% empty values can reduce the accuracy of data analysis significantly. Removing these empty elements helps streamline your reports and enhances clarity.

But don’t worry—Power Query has the perfect solution for you.

Custom M Function: Your New Best Friend

Now, let's get to the good part. By using a custom M function, you can automate the process of removing empty elements every time you load a new table. This function is reusable, meaning you won’t need to go through the same steps repeatedly!

Here’s a straightforward M code snippet you can use in Power Query:

let

    RemoveEmptyRows = (tbl as table) =>

        Table.SelectRows(tbl, each List.NonNullCount(Record.ToList(_)) > 0),

    RemoveEmptyColumns = (tbl as table) =>

        Table.SelectColumns(tbl, List.Select(Table.ColumnNames(tbl), (col) => List.NonNullCount(Table.Column(tbl, col)) > 0)),

    CleanedTable = (tbl as table) => RemoveEmptyColumns(RemoveEmptyRows(tbl))

in

    CleanedTable

This code defines three steps: it removes empty rows, eliminates empty columns, and then combines both processes into a single cleaned table. By using it, you are ensuring that your dataset remains insightful and engaging.

Step-by-Step Breakdown

Let’s break down that M code to see how each part works.

1. `RemoveEmptyRows`: This function selects rows that have at least one non-null value. For instance, if you have a table with 1,000 rows and 100 have at least one value, this function keeps those 100 rows while discarding the empty ones.

   
   

2. `RemoveEmptyColumns`: This function checks each column for non-null values. If a column has no values, it gets removed. Imagine you have 20 columns and 5 of them are completely empty; those 5 are eliminated.

   
   

3. `CleanedTable`: This function wraps everything up and gives you a neat and tidy table. You can apply this function whenever you deal with a new dataset.

   
   

Now, whenever you receive fresh data, just run this function and, in seconds, you’ll have a clean and organized table at your fingertips.

How to Use It

Using this custom M function in Power Query is quite simple. Just copy the code into the Advanced Editor, replace your target table name, and apply the changes. It’s like dressing your data in a crisp new outfit!

Step 1: Open Blank Query in Excel or Power BI

Blank Query UI

Step 2: Copy/paste M code in Advanced Editor and click OK

Advanced Editor UI

Step 3: Rename your new function into "fx_Remove xyz.."

Custom function created

Step 4: Invoke function on desired column and magic happened!

from this table below:

Empty columns to remove

to this table:

Removed columns by triggering function

Final Thoughts

Cleaning up those empty rows and columns may feel like a chore, but with the help of M functions in Power Query, it becomes a straightforward task! This approach saves you time and boosts your dataset's clarity, transforming a cluttered table into one that sparkles with potential insights.

So next time you encounter a messy dataset, remember this custom function. You'll be ready to tackle any data challenge that comes your way with confidence. Happy querying!

Get your M-code ready, use it reuse it in Power Query:

fx_remove empty column and rows.txt

Download TXT • 1KB