Fill Up/Down when values are shifted | Advance Fill down/up in Power Query

In this tutorial, we will look at two scenarios we can use to Fill up and down when the values are in the wrong position or are shifted.

These are the scenarios:
Scenario 1: The values you need to fill down are shifted on the same column
Secnario 2: The values you need to fill down are shifted on different columns

Before	After
Before	After

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

1. Scenario 1: Shifted values on rows

The values you want to fill down are shifted in the same column:

For each set in Column2 that begins with A, if any corresponding value in Column1 is null, it must be filled with the last value above or below.

Create a Custom column

1. Navigate to Add Column tab
2. Click Custom Column

Enter the below formula,

if [Column2]="A" and [Column1]=null then 1 else [Column1]

FillDown values for each block.

1. Right-click the newly created custom column
2. Select Fill -> Down

The result should fill all the null value cells in Column1 with the nearest available value in its block.

Replace the unique values with null.

1. Select the newly created custom column
2. Navigate to the Home tab
3. Click Replace Values

Replace the unique value with null. In my case, the unique value I provided in the formula is 1

FillUp values for each block

1. Right-click the newly created custom column
2. Select Fill -> Up

The result should Fill all the remaining null value cells with the nearest available value

We have successfully filled the data for each block with its respective value in the Custom column.

2. Scenario 2: Shifted values on columns

The values you need to fill down are shifted on different columns

Lets start by Fill Down Column1

1. Right-click the Column1
2. Select Fill -> Down

The result should Fill all the null value cells with the nearest available value above it:

Unpivot Column1

1. Select Column1
2. Navigate Transform tab
3. In the Unpivot columns drop-down, select Unpivot Other Columns

The result of unpivot should look like below:

Pivot the columns back

1. Select Attribute and Value column
2. Navigate to the Transform tab and click Pivot Column
3. In the Values Column drop-down, select Value
4. In Aggregate Value Functions, select Don’t Aggregate
5. Click Ok

The result should look like below:

We have successfully loaded Column1s’ respective Column 2 to 5 values in a single row.

3. Learn more

To learn more about the use cases of the FillUp and FillDown functions, kindly refer to the blogs listed below:

Power Query wont fill up or down the values

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