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:
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
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.
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
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.
. All null value cells are replaced with the last available value above them:
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
How to Fill Up/Down a column in a grouped table in Power Query