Introduction
“Fill Down” and “Fill Up” are powerful features of Power Query, a data preparation and transformation tool in Power BI. These features are used to populate values within a column based on the values of other cells in the same column.
These operations are extremely helpful when dealing with datasets that contain null values, making data standardization and cleaning easier. When using Power Query, Fill Down and Fill Up are valuable features for manipulating and preparing your data efficiently and consistently!
In this post, I will explain to you what these features are, as well as showcase some of their peculiarities using different scenarios.
Scenario 1: Fill Missing Values using Fill Down
Let’s suppose we have the following scenario:
In our Power BI Data Model, we have a table containing customer information, the Customer Code, and Ending Balance values. However, as you can see from the image below there are some records with missing values and we need them to be filled in.
In other words, the data must be transformed so that we always have a consistent value for each customer. For example, in the case of Customer Code C001, there are several rows without an Ending Balance value, but the expected value for all records is 100.
The question is: How can we achieve the expected results?
It’s simpler than it seems! For this purpose, we can use the Fill Down feature in Power Query.
To do this, we need to click on the column we want to fill and then, in the “Transform” tab, click on “Fill Down”.
And voilá… The null values have been filled as we expected! 🙂
Scenario 2: Fill Missing Values using Fill Up
In a second scenario, we have missing values we need to fill in but this time we want to do an upward fill, that is, filling the missing values with the same value below that is not null.
Let’s look at this example:
Scenario 3: Fill Down/Fill Up Grouped by another column
In addition to the two scenarios presented earlier, we have a third one, slightly different.
Let’s suppose we have a table like the one in the image below, and now instead of just one customer, we have information for multiple customers.
And the expected outcome is something like this:
For this scenario, I will present you with two solutions to solve the problem.
Since we have data from multiple customers, from the image above we can see that we need to perform a Fill Down first and then a Fill Up to be all to fill in all missing values.
The first step is to Group the data by Customer because remember: We now have multiple customers, and it’s important to group them before performing the filling, otherwise, the values won’t be filled correctly.
Solution 1
Step 1: Group by Customer No
First, we need to group by Customer No. To do so, you should follow the steps demonstrated below:
Finally we just need to expand the column and voilà….We will have the expected result with the values correctly filled!
Although this solution is quite intuitive and easy to use, we are creating a new additional column and, for that reason, it might be more interesting to avoid creating this column and performing the filling in the same column, that is, in the Ending balance column.
In other words, it might be interesting and easier to understand if you Group by Customer and perform the Fill Down in one step, and then do the same for the Fill Up in another step.
Solution 2
For this second solution, first, we need add a new step in Power Query.
Then, in the new step, we can use Table. Group function from the M language to group directly by Customer No, and it avoids a new step for Grouping.
Finally, we apply the Fill Down and expand the result. All of this is in the same step!
After expanding the values, we can see that part of the problem is already solved because the values are now filled in descending order.
Now, we need to apply a similar logic, but this time performing a “Fill Up”.
With this solution, we avoid an additional column and we are able to perform the filling in the same column!
Conclusion
To conclude, we can say that Fill Down and Fill Up are versatile features and highly useful for efficient data manipulation and cleaning complex datasets.
The ability to fill in missing values based on existing data provides an effective way to ensure data consistency. These operations are particularly valuable when dealing with gaps or null values in columns, offering flexibility in data preparation and transformation in environments such as Power BI or Excel. Incorporating these functionalities into data transformation steps can significantly simplify the data handling process and ensure accuracy and uniformity in the analyzed datasets.
0 Comments