Using Fill Down or Fill Up in Excel Power Query

In our overview blog on the excel power query feature, we had learned about the basics and the introduction of power query in Excel. We also learned how the power query feature proves to be a useful tool in Excel when you want to get data from other excel sources like PDFs, Text & CSV files, JSON, Websites or web pages, etc. In this blog, we would learn how to use the fill down and fill up feature in excel Power Query.

If you are new to excel power query feature, I strongly recommend you to go through the ExcelUnlocked overview blog on Power Query Feature.

Meaning of Fill Down and Fill Up in Excel (with Examples)

Fill Down: This feature allows you to copy the value above the blank cells to all the blank cells below it.

Fill Down Example

Fill Up: This feature allows you to copy the value below the blank cell(s) to all the blank cells above it.

Fill Up Example

Now, when you know what does fill down and fill up mean, let us begin with this tutorial on how to use this tool in an excel power query.

Fill Down & Fill Up in Excel Power Query [Step by Step Guide]

This involves 2 basic steps:

  1. Importing data from excel into Power Query Editor window
  2. Filling the values up or down in the Power Query Editor window

Importing Data from Excel into Power Query Editor Window

In order to import and work with a tabular dataset in power query, it is must that you have an excel understandable table format. The excel power query feature does not understand a normal excel dataset table.

To convert a normal dataset table into an excel understandable table format, select the table range, and then navigate to the ‘Insert’ tab > ‘Table’ group > ‘Table’ option and click OK. Or use the ‘Ctrl + T’ keyboard shortcut.

Steps to Convert Normal Table to Excel Table

Now, let us learn how to import this excel table into power query editor window.

Click anywhere on the table and then follow this path – Data > Get Data > From Other Sources > From Table/Range.

Get Data From Table Range Navigation

As a result, you would see your excel table in the power query editor window. Also, you would notice that the default name of this window is the Table Name. See the image below.

Table Imported in Power Query Editor Window

Here, the term ‘Null’ in the Power Query Editor window refers to those cells with no values in it (i.e. the blank cells).

Using Fill Down and Fill Up in Power Query

Filling Values Down or Up in Power Query Editor Window

Once the data is imported to Power Query Editor window, follow the below steps:

  • Click on the column header ‘Date’ to select the entire column.
  • Now, go to the ‘Transform’ tab > ‘Any Column’ group > ‘Fill’. Here you would find both the options – Down and Up. Choose the option ‘Down’ to fill the values down.
Steps to Fill Down Power Query Editor Window

As a result, you would notice it would copy the value above to all the blank cells below in the entire selected column.

Fill Down Result in Excel Power Query

The ‘Date’ column in the transformed table above also shows the time value ’00:00:00′. If you do not need the time, then you can change the column data type from ‘Date/Time’ to ‘Date’. To do so, select the ‘Date’ column, and then go to the Transform tab, click on ‘Data Type: XXXX’ and choose ‘Date’.

Change Data Types of Date Column

This would instantly, remove the time value ’00:00:00′ from the date column.

This is why I call Excel Power Query feature as a magical life saver tool 😉 So much easy to use with instant results !!

Similarly, you can use the ‘Up’ option under the ‘Fill’ drop-down to copy the value below to all the blank cells above that value.

Importing Table Back from Power Query Editor to Worksheet

Finally, let us import back the new transformed table from power query editor window to excel worksheet.

Go to the ‘Home’ tab > ‘Close’ group > ‘Close & Load’ option (see image below).

Power Query Editor - Close & Load Icon

This would instantly load the new table from power query editor window back to excel in a new worksheet.

Leave a Comment