How to Fill Down Blank or Null Cells in Power Query - Excel Campus
10

How to Fill Down Blank or Null Cells in Power Query

Power Query (aka Get & Transform) has a Fill function that allows us to fill cells with the value above or below.

This helps when your data contains cells or rows with blank (null) values, and you want to copy down the value from the cell above.

Power Query Fill Down Blank or Null Cells with Values

To use the Fill function in the Power Query Editor:

  1. Right-click the column header of the column you want to fill.
  2. Select Fill > Down from the right-click menu.

The values will be filled down to replace the null values with the value above.  The Fill function will NOT replace any existing values in the column.  Once it hits a cell with a value, it will then look for the next blank cell and fill it with the value above.

Please leave a comment below with any questions.

Jon Acampora
 

Welcome to Excel Campus! I am excited you are here.
My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career.
I’ve been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP.
I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I’m not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 10 comments
Tyler - December 14, 2017

I had the same problem – the solution was to change the column’s data format from default “ABC123” (text) to a number format. After that, I deleted the fill step and added it again and it worked.

Reply
    Jon Acampora - December 15, 2017

    Thanks for sharing this solution Tyler! The text data type is probably treating the null value as text (the word null) instead of a null value (blank).

    Reply
Komlan AGODO - October 17, 2017

For those having issues with fill down on blank cells, you need first to replace those blank cells by “null” value using replace values function on the given column. After this change, the fill down function work perfectly. For I don’t know which reason the fill down function is treating blank or empty cells like they have some kind of data. putting “null” inside those cells is the workaround.

Reply
    Jon Acampora - December 15, 2017

    Thank you Komlan! Yes, Power Query typically displays blank cells as null values. However, if the cells in the Query Editor are not null for some reason, then we can use the Replace feature to replace blank with null.

    Reply
susan - May 29, 2017

I have trouble to use fill up/down function. It does not work.

Reply
claudio - February 16, 2017

That is great!!!! I have been programming in VBA for 6 years for personal project nothing profession. This fill down power query is great. I love the short video clip. Thank you for putting this together.

Reply
Ali - September 17, 2016

I have MS Office 2016 which has “Get & Transform” version of Power Query. In this version of my MS Office, this filling functionality is not working despite it has been recorded as a step in “Advanced Editor”. All blank cells are not being filled and remained blank. So what could be the reason and how to overcome it?

Reply
    Jon Acampora - September 27, 2016

    Hi Ali,
    That sounds like a different problem. The Fill function in PQ is going to fill up/down blank cells in the table in PQ. This article is referring to formulas in the worksheet that are not being copied down when the table is loaded to the worksheet or refreshed.

    Reply
      Jonathan - July 31, 2017

      Hi,

      I think the real problem that the user is stating is that the fill down feature does not work for blank cells that have been created within Power Query, only the “null” cells that were brought in. For example, I’ve manipulated one column to extract text before a delimiter, and it leaves behind many blank cells. The fill down feature does not work on these. Any workarounds for this?

      Reply
    Marco Hames Moreno Mora - May 10, 2017

    Hi

    Igualmente have the dame trouble, any idea?

    Reply

Leave a Reply: