The 2nd Edition of #25daysofdaxfridays challenge is here!
How can we help?

Welcome to Curbal Learning Portal. Search for answers using the search box below,
or create a request for a video or blog if you cannot find your answer.

Popular searches: DAX Glossary | Guided courses | Request a video

# 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

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.