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.

I also have an article on how to fill blank cells horizontally across rows.

Please leave a comment below with any questions.

17 comments

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

  • Hi,
    I have three columns Year, Month and AdvertisingExpense. The AdvertisingExpense value is blank for most year-month combination. How can I update only these blank values with the average value for year-month combination. As an example:
    Year Month AdvExp
    2009 Jan 200
    2009 Feb 100
    2009 Mar
    2009 Apr 200
    2009 May

    How do I update AdvExp column for 2009-Mar and 2009-May with the average of the other values. There are other year-month for 2010, 2011 and so on.

  • You can certainly see your enthusiasm in the article you write.
    The world hopes for even more passionate writers like you who are not
    afraid to mention how they believe. Always go after your heart.

  • Hi

    I have a large dataset with historic prices for different cities. I can use this method to fill missing prices for most incidents, however if the price for the first date for a city is missing I dont want that price to be the same as the above price, because this price will be the price of the last date by another city..

    How can i solve this problem?

  • 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.

  • 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.

    • 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.

  • 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.

  • 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?

    • 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.

      • 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?

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly