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.

6 thoughts on “Power Query Fill Down Doesn’t Work

  1. Thanks for the tips, had just encountered same situation myself. Was hoping there is some sort of option parameters in ‘Fill Down/Up’ clause of M-Code, to include all the empty, blank and null entries to replace, is there such option? I’m not that deep into the M-Code at the moment, so, just wondering.

    • Hi Tony
      I am not an expert in M code either. Since the solution is easy and it works I didn’t investigate any further.
      I try to avoid editing M code and leave that as a last resort if I can’t do something via the interface.

      • Hi Neale
        True to that, only using the interface in Power Query doing my ETL so far, unsure if it’s worth it to get more involve in learning M-code. I think DAX more worth it to get skills and experiences in.
        Also like to mention how cells ended up as empty, blank, or null entries isn’t always obvious and straight forward to spot in source file, probably can be a topic of its own.