Replace Missing Values in Power Query With an Average

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:

A data table in Power Query which is missing values.

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.

Select the average function in Power Query.

Power Query now creates a separate step for me which has calculated the average:

Power Query calculating the average of the Price field.

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:

Replacing values in Power Query.

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.

Replacing null values with a 1 in Power Query.

In the formula bar, replace the ‘1’ with the name of the step — AveragePrice:

Adjusting a formula in Power Query so that it references the average price.

You’ll get an error saying there is a circular reference:

A cyclic reference in Power Query.

To fix this, drag the new step you just created so that it comes after the AveragePrice calculation step.

Moving the replaced value step in below the AveragePrice step in Power Query.

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.

Adjusting the Power Query formula so that it references the Source step.

Now the field is correctly updated and the null values have been replaced with the average for the column:

Power Query table after the null values have been replaced with an average.

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.

Comments are closed.