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.

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

Please leave a comment below with any questions.

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

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

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

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

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

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

  • Perfect article, thanks – it gave me the hint I needed regarding why my Fill Down wasn’t working. The data I was importing was an empty string (“”) and not null. So once I converted empty strings to nulls and THEN did the fill down it worked a treat.

  • Search
    Generic filters
    Exact matches only

    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

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    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

    Free Excel Training Webinar Modern Power Tools

    >