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.
To use the Fill function in the Power Query Editor:
- Right-click the column header of the column you want to fill.
- 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?
Hi
Igualmente have the dame trouble, any idea?
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 trouble to use fill up/down function. It does not work.
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.
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).
Can this be possible for Right fill and left fill in ROWs
Great question! Yes, it is possible.
I just created a new article with instructions and a screencast. Here is a link to the article on how to fill across blank rows with Power Query
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.
Can you fill down a group of values, rather than the last single value?
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.
Thanks, this is very helpful. The trick of replacing BLANK with NULL enabled this feature to work with me on text columns.
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.
Hi, my source data has merged cells that look like they come across unmerged into Power Query, but the fill down does not then work on them – is there anything I can do to sort this?
I should have read the previous posts before submitting my comment as I have not replaced the blanks with nulls and the fill down is working a treat! Thanks so much – you are all very clever!
only 1 column from the table appears in power query. The remaining all populated the word Null.
My issue that this did not work as I had BLANK values, not NULL values! I did a replace BLANK > NULL. Then the Fill Down work perfectly.