Fill values in column in Power BI

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

Split Columns By Positions

Choose and Remove Columns from table

Data Profiling: Column Quality, Column Distribution & Column Profile

 303 total views,  3 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.