Table of Contents
Power BI is a powerful business intelligence tool that can be utilized to automatically fill in blank values with the previous value in a dataset. This feature can be particularly useful when dealing with large datasets that may contain missing or incomplete values. By using Power BI’s data transformation capabilities, users can easily create a data cleaning process that automatically scans for and replaces any blank values with the most recent previous value. This can save time and effort for users who would otherwise have to manually fill in these missing values. Additionally, this feature helps to ensure the accuracy and completeness of the dataset, making it a valuable tool for data analysis and decision making.
Power BI: Fill Blanks with Previous Value
Often you may want to fill blank values in a column in Power BI with the previous value in the column.
The easiest way to do this is by using the Fill feature in the Transform tab of the Power Query Editor in Power BI.
The following example shows how to use this feature in practice.
Example: How to Fill Blanks with Previous Value in Power BI
Suppose we have the following table in Power BI named my_data that contains information about basketball players on various teams:
Notice that there are several rows in the Points column that contain blanks.
Suppose we would like to replace each of these blanks with the previous value in the column.
To do so, click the Transform data icon on the Home tab:
Then click the Transform tab, then click the Fill icon, then click Down from the dropdown list:
This will automatically fill each blank value in the Points column with the previous value in the column:
Note: If you would like the blank values to be filled using the next value in the column then click the Up option from the Fill dropdown list instead.
Once you attempt to exit out of the Power Query Editor, a message box will appear that asks if you would like to apply the changes.
Note: You can also fill blank values in multiple columns at once by simply selecting multiple columns before clicking the Fill dropdown menu.
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: