Power Query provides a FILL operation which allows you to fill up and fill down to replace null values with the last non-empty value in a column.
Lets look at an example of using a fill up and fill down feature in Power BI.
As you can see a below sample dataset named Item. There you can see in column Qty1 and Qty2 are having some null values.
Copy sample data for practice:
| ItemCode | Qty1 | Qty2 |
| AC-89Y | 5000 | 5000 |
| BV-67H | 4000 | |
| VB-6J7 | ||
| DF-BN2 | 3500 | |
| EF-32W | 3000 | |
| SD-78E | ||
| GS-W7E | 2000 | |
| HF-34D | 1000 |
Once you load data in Power BI Desktop, lets click on Transform data tab in ribbon bar.
Once you click on Transform data, a Power Query Editor window opens.
Using a Fill down Operation
Lets replace the null values for Qty1 column as shown below using a fill down operation.
In this operation a values will be filled down to replace the null values with the value above, basically takes a column and traverses through the values in it to fill any null values in the next rows until it finds a new value. This process continues on a row-by-row basis until there are no more null values in that column.
There are two way in the user interface where you can find Fill operation in Power Query Editor, the first one is in the Data Preview screen you can simply go to column header (in this case column Qty1) and right click then in context menu navigate a Fill operation and select Down.
And the second way is the in Transform tab, click on Fill dropdown and select Down operation.
Once you click on Down, you can see the values are filled down to replace the null values with the last non-blank values above.
Using a Fill up Operation
Lets replace the null values for Qty2 column as shown below using a fill up operation.
In the same way as the fill down operation, fill up works on a column. But by contrast, fill up finds the last value of the column and fills any null values in the previous rows until it finds a new value. Then the same process occurs for that value. This process continues until there are no more values in that column.
Fill up operation can also be found in user interface in same way as you have seen above for Fill down. So in similar way you can either right click on column header (in this case column Qty2) then navigate to Fill and select up or select column Qty2 first then in transform tab, go to Fill operation and select Up from dropdown as shown below.
Once you click on fill up, you can see it finds the last value of the column and fills the null values in the previous rows until it finds a new value.
Also Read..
Pivot columns – turn rows into columns
Choose and Remove Columns from table
Data Profiling: Column Quality, Column Distribution & Column Profile
