New JSON theme available! Check it out 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

Print

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

BeforeAfter
BeforeAfter
fill up down power queryfill up down power query
Advance Power Query Fill up/down scenarios

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

Advanced fill down scenarios with Power Query

1. Scenario 1: Shifted values on rows

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

Values shifted in same column In Power Query – fill down

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
The values you need to fill down are shifted on the same column in Power Query

Enter the below formula,

if [Column2]="A" and [Column1]=null then 1 else [Column1]
Custom column Power Query

FillDown values for each block.

  1. Right-click the newly created custom column
  2. Select Fill -> Down
Fill down values in Power query

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

Was this helpful?
Previous Split multiple columns into rows without errors in Power Query
Next Remove duplicates on multiple columns in Power Query

Reader Interactions

Let us know your thoughts...

Table of Contents