Filling empties in a dataset with column averages

[Quixote]

I have a dataset where all the values are numbers minus some of them which are empty values.

I need to find how to fill those empties with the column averages using Get Data/Power Query.

Thanks,

PS: Sorry for my English

[KGrice]

Is there a need for this to be in the Get Data part, as opposed to after loading the data? This is probably much simpler as a calculated column with DAX.

For example, I created a list of random numbers, then loaded it into Power BI Desktop. I left the blanks alone in Get Data, then went to the Data pane to look at the table (not strictly necessary, but helps to visualize what's happening). From the Modeling tab in the ribbon, select New Column, and set the formula as

Column = IF(ISBLANK(TableName[Numbers]), AVERAGE(TableName[Numbers]), TableName[Numbers])

Here's what I end up with:

You can also round the average number, if you need it to be more like the other numbers in the column.

[jahida]

Original question asked for a solution in M, so although KGrice's solution will work, here's the M alternative:

Add a custom column using this code:

= if [Numbers] = null then List.Average(#"Added Custom"[Numbers]) else [Numbers]

Obviously you'll have to change the "Added Custom" and "Numbers" to the relevant step and column names. Additionally, this formula might be really slow. I'd imagine a decent query processor would be able to optimize to only do the Average calculation once but maybe not... I don't have a large dataset to test on. If it is really bad for efficiency, you can pull out the Average into another step. Let me know if you'd like M code for that.

[Quixote]

Thank you very much Kgrice and jahida!

I prefer the M solution because I think is better in order to have a live connection with de source data.

And yes, I would like to have the M code for pull out the Average into another step. Thanks again jahida.

PS: Sorry for my English.

[jahida · Accepted solution]

Your English is fine, don't worry about it. Here's the M code in two steps (you can plug this into the advanced editor or make a garbage step and change the code):

    #"Ave" = List.Average(#"Changed Type"[Numbers]),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Numbers] = null then #"Ave" else [Numbers]),

Then you can delete the initial column (in this case, [Numbers]). This should only calculate the average once.

[Quixote]

Very nice. Thanks.