Power Query Fill Down Doesn’t Work

The fix is easy

Sometimes when working with CSV files in Power Query you may strike the situation where Fill Down doesn’t fill down. Don’t worry there is an easy fix.

The problem is how Power Query treats empty, blank and null entries. We see empty and blank cells as blanks in the data whereas null cells have the word null in them. By the way null is ALWAYS in lowercase in Power Query.

Power Query will always fill down and replace null cells. It doesn’t always do that with blank and empty entries, especially from CSV files. The screen shot below is from a CSV file. we want to Fill Down on the Campus column. You can try this yourself. The CSV file can be downloaded via the button at the bottom of the post.

The blank entries in the Campus column don’t contain anything but if you use Fill Down nothing happens. See screen shot below.

Whilst this is frustrating the solution is easy. You can replace nothing with null.

Right click the Campus column and Choose Replace Values. Don’t type anything in the top box and type null in the bottom box (remember null is always in lowercase). See image below and click OK.

As you can see this populates all the empty/blank cells with null and now Fill Down will work.

Right click the column header and choose Fill, then Down and the job is done.

This appears to be a CSV issue as empty/blank cells in Excel tables are handled correctly and display null.

Download example CSV file

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

One thought on “Power Query Fill Down Doesn’t Work