New JSON theme available! Check it out here!
Print

Troubleshoot Fill Up/Down cells with values above or below in Power Query | Fill up and down functions

Fill Up and Fill Down functions in Power Query allow users to fill blank cells with the value above or below. This is useful when you have cells blank values and want to use the last available value from above.

Sometimes, the functions Fill down and Fill up wont work, and here I will show you why,

If you prefer to watch a video instead, click below:

Fill down not working in Power Query?

You will learn:

1. Show the example
2. Replace blanks in your data
3. Use Fill down or up to get the last available value
4. Other Fill down/up scenarios

1. Example

In this example, we’ll see how to use Power Query’s Fill Down function to replace null values in Columns 1 and 2 with the last available value.


Existing data:

Before fill down or up in Power Query

Expected Result:

After fill down in Power Query

2.Troubleshoot Fill up/down

If you try to use the Fill down or up on the table shown above you will see that nothing happens because:

The FillUp and FillDown function only work for null value cells. If the cell has empty text, this does not work.

Curbal

In order to fix this, set the value of empty text cells to null.

1. Select the columns in which you want to replace empty cells with the null value.

2. Navigate to the Home tab -> Replace values

Replace values in Power Query

 3. In the replace dialog box, leave the Value to Find textbox as it is, enter null in the Replace With textbox, and click Ok.

The empty text cells are replaced with null values, as shown below.

3. Fill up or down on multiple columns

1. Press the Shift key and select Columns 1 and 2.

2. Right-click the column and select Fill -> Down.

Fill down or up in Power Query

. All null value cells are replaced with the last available value above them:

Fill down in Power Query

4. Other Fill Down/Up cases

Using the FillDown function in the Power query, we successfully loaded Columns 1 and 2 with their respective values. Similarly, we can use the FillUp function to fill records based on the first available value below.

To learn more about the advanced use cases of the FillUp and FillDown functions, kindly refer to the videos listed below:
How to use Fill Up & Fill Down functions in Power Query in Advanced Scenarios

Advanced Fill down/up scenarios in Power Query

How to Fill Up/Down a column in a grouped table in Power Query

Fill up/down on grouped tables

Was this helpful?
Next Fill Up/Down when values are shifted | Advance Fill down/up in Power Query

Reader Interactions

Let us know your thoughts...

Table of Contents