A common problem you might come across with data is that it may sometimes contain missing values. You can remove the data entirely or you can replace the values with something else. If you remove it, you might throw away other useful data related to that record in the process. If you replace it, you don’t want to just replace missing values with a zero, as that can impact your calculations. The optimal choice may be to replace it with the average of the other values. In this post, I’ll show you how you can easily do that in Power Query.
Calculating an average in Power Query
Here is the data set I’m starting with in Power Query, where you’ll see numerous null values in the Price field:
To calculate an average in Power Query, follow these steps:
Select the column you want to calculate the average for. On the Transform tab, select Statistics and select Average.
Power Query now creates a separate step for me which has calculated the average:
To easily reference this value later on, I’m going to rename this step as AveragePrice.
Replacing the values
Now that you have calculated the average, you can use it to replace the null values in your data set. To do this, you’ll again need to highlight the column which contains null values. Right-click and select Replace Values:
Enter ‘null’ as the value you want to replace and just enter ‘1’ as the value to replace it with. This will just be a temporary placeholder.
In the formula bar, replace the ‘1’ with the name of the step — AveragePrice:
You’ll get an error saying there is a circular reference:
To fix this, drag the new step you just created so that it comes after the AveragePrice calculation step.
This still results in an error, and that’s because the formula is now referencing the AveragePrice step. This needs to be adjusted so that it references the Source step — or the one which contains the data immediately before the average price calculation.
Now the field is correctly updated and the null values have been replaced with the average for the column:
In this situation, we now have eliminated the null values while being able to keep the other fields and simply replaced the empty values with averages.
If you liked this post on how to Replace Missing Values in Power Query With an Average, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.