• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Remove Blank Rows and Columns from Tables in Power Query

You are here: Home / Power Query / Remove Blank Rows and Columns from Tables in Power Query
delete empty rows and columns using power query
April 21, 2022 by Philip Treacy

When you import data to Power Query, it's not unusual to end up with a table containing blank rows and columns, like this

starting data table with blank rows and columns

There are a few ways to get rid of these 'blank' rows and columns, some better than others.

Watch the Video

Subscribe YouTube


Download Example Excel Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download Excel Workbook


This Works In Power BI and in Excel

The M code shown here can be used in Power Query in both Power BI and Excel.

Unpivot and Pivot

The first method involves unpivoting and then pivoting the data. This works because when you unpivot, Power Query automatically removes any null values.

Start by adding an Index column

ad index column

index column added

Select the Index column, right click the column header and then click Unpivot Other Columns

unpivot other columns

unpivoted table

Next, select the Attribute column and from the Ribbon, Pivot that column

pivot the column

In the Pivot column options, choose the Value column as the values. In the Advanced Options, choose not to aggregate

pivot column options

Giving this table as the result (I've deleted the Index column).

pivoted table

Not all the 'blank' rows and columns have been removed, I'll get to that shortly. But first I want to point out that by using this method, you lose any data type changes that were made before the unpivot/pivot.

Before Unpivot/Pivot

before the unpivot pivot

Why Aren't All the 'Blank' Rows and Columns Removed?

Because they contain something other than null. Unpivot/Pivot will remove any nulls, but if you have things like spaces, empty strings or non-printing characters like tab or carriage return, these all get left unchanged.

Looking closely at the source data table, you can see that the first 3 rows on the Init column contain spaces (trust me), and rows 4, 5, 6 and 7 contain non-printing characters. CHAR(9) is tab and CHAR(13) is carriage return.

data table with non-null values

Another approach is needed to deal with these types of characters.

Writing M Code

OK, so the better approach requires writing some M code. Sorry, you can't get away from this. But if you don't want to write the required code (or even learn how it works) then you can just download the example Excel workbook I created and use the function I wrote. No need to know how it works, just use it.

Removing Blank Rows

To start, I'm going to use the Ribbon in Power Query to Remove Blank Rows (removing columns comes later).

remove blank rows using the ribbon

This results in a new step with this code (I've formatted it so it's easier to read)

basic m code to remove blank rows

This code needs some additions to deal with spaces and non-printing characters.

The following code is the result of these additions. I've color coded it and formatted it to try to make it easier to understand my explanation of what it does. You can also watch the video for my explanation.

remove blank rows using the ribbon

1. Record.FieldValues passes the values in each row to List.RemoveMatchingItems

2. List.RemoveMatchingItems removes empty strings "" and null values

3. List.Transform uses Text.Clean and Text.Trim to remove spaces and non-printing characters from the items in the list produced by Step 2. Text.Clean and Text.Trim replace the values they remove with an empty string.

4. List.RemoveMatchingItems removes empty strings "" produced by Step 3

5 and 6. Select rows from the table that are not empty

Removing Blank Columns

This code is hand written and is shown in the image below.

code to remove blank columns

1 and 2. Table.Column goes through each column (selected by Table.ColumnNames) and passes the values in each column to List.Transform

3. List.Transform uses Text.Clean and Text.Trim to remove spaces and non-printing characters from the items in the list (each column). If Text.Clean and Text.Trim result in an empty string, replace that with null.

4. List.NonNullCount counts the number of non-null items in the list (the column)

5. List.Select uses the results from List.NonNullCount to create a list of the column names that contain data

6. Table.SelectColumns uses this list to select only those columns into a new table

The result is this

table with blank columns and rows removed

Creating a Function From This Code

Whilst writing all of that would be fun every time you needed it, it'd be easier to use a function to clean up your tables.

To create a function from this code, first of all duplicate the query, and call it something useful like fxRemoveBlanks.

Open the function query in the Advanced Editor and you'll see this.

code before conversion to function

The changes needed to make this code a function are:

1. Add the function declaration and pass in a single parameter called Source which will contain the table

2. Delete the first 2 lines of code

3. Change the table being worked on in the #"Removed Blank Rows" step to Source

Resulting in this

code as m function

Using the Function

If you have a query like this where the Source is loaded in the first step

source without function

Just call the function, passing in the code that loads the table as the function's parameter.

source calling the function

Or, at any point in your code, you can call the function as long as you pass in a table as the parameter.

delete empty rows and columns using power query

More Power Query Custom Functions Posts

remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
grouped running totals in power query

Grouped Running Totals in Power Query

Create grouped running totals with a Power Query custom function. Simple to use and extremely fast. Example workbook with code to download.
quickly create running totals in power query

Quickly Create Running Totals in Power Query

Create running totals in Power Query using super fast queries that can process 10's of 1000's of records in the blink of an eye.
power query custom functions

Power Query Custom Functions

Create functions in Power Query to provide functionality that isn't built in - from simple things like currency conversions to scraping websites, and more.

More Power Query Posts

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.


Category: Power QueryTag: Power Query Custom Functions
Previous Post:extracting data from lists and records in power queryExtracting Data from Nested Lists and Records in Power Query
Next Post:Excel Student Performance Dashboardexcel student performance dashboard

Reader Interactions

Comments

  1. Chris Warren

    March 30, 2023 at 11:03 am

    Hi Philip, I finally had cause to use this fabulous technique and I came across a limitation to it being that the code will fail if there are any errors (like #N/A) in the data being imported. Now we can remove these prior to using your technique, but since your code parses the whole data, I was wondering if your code could be expanded to handle / ignore / treat-as-blank any error values it finds, or should I remove all errors first with something like this:

    #”Replaced Errors” = Table.ReplaceErrorValues( Source, Table.ToColumns( Table.Transpose( Table.AddColumn( Table.FromList( Table.ColumnNames(Source), Splitter.SplitByNothing(), null, null, ExtraValues.Ignore), “Custom”, each null))))

    Reply
    • Philip Treacy

      April 10, 2023 at 3:38 pm

      Hi Chris,

      Modifying my code would involve adding something like you’ve already written, so you may as well just add your own code to remove the errors.

      Regards

      Phil

      Reply
  2. Paul Clark

    September 21, 2022 at 1:43 am

    Is it possible to have this example in Power BI format with the functions to follow along the video?

    Reply
    • Mynda Treacy

      September 21, 2022 at 8:55 am

      Hi Paul,

      Power Query in Power BI is the same as Power Query in Excel, so you should be able to follow along, but if you get stuck please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Mynda

      Reply
  3. Farshad

    May 15, 2022 at 11:32 pm

    These posts are great and useful Thanks to the treacy family

    Reply
    • Philip Treacy

      May 16, 2022 at 8:56 am

      You’re welcome Farshad, glad it was helpful.

      Reply
  4. Jim Fitch

    April 25, 2022 at 11:26 am

    Phil, This is awesome. I have needed this for some time. I read through this a few times & follow the code, but I doubt that I would ever have figured this out on my own. Because of that, I’ve copied both the Code & function into my ExcelTips workbook for future use. Thanks for doing all the hard work to develop this.

    Reply
    • Philip Treacy

      April 26, 2022 at 10:08 am

      No worries Jim, glad you found it useful.

      Regards

      Phil

      Reply
  5. Mustafa

    April 22, 2022 at 11:17 pm

    Hi Phil,

    The function you created is really useful and the video expalantion was great. If I want to use the function regularly in other queries what is the best way to do this? Is it just a case of copying the code from the advanced editor and creating a new query in each instance or is there a better way to share functions between files?

    Thanks

    Mustafa

    Reply
    • Philip Treacy

      April 23, 2022 at 12:56 pm

      Thanks Mustafa.

      The quickest way I find to copy queries is to click the query name in the original file, CTRL+C to copy it, then in the new file, CTRL+V and the entire query will paste in.

      Cheers

      Phil

      Reply
      • abha

        June 2, 2022 at 1:10 am

        The function given is so easy to use.
        What I do is deleting manually blank columns and then transpose and changing data type and again manually deleting blank columns and again transpose.

        Reply
        • Philip Treacy

          June 3, 2022 at 12:18 pm

          Thanks Abha

          Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

launch excel macros course excel vba course

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x