Cheat Sheet: Power Query Replace Value on all columns and Replace null with Average Value

Power BI: Power Query Replace Value

Problem: 1. Need to replace a value across all the columns.
2. Need to replace the null value across all columns with n/a.
3. Can we replace the null value of a numeric column with the Average Value of that column?

Replace Value

Data — I have two columns. I want to replace the GG value with ABC and null with N/A. Row three in the data below has the GG value.

Power BI Replace Values

Select both columns, Then click on the Transform data tab → Replace Value → Replace Value

Power bi replace values

Replace GG → ABC

I got

Replace Value

Check Row 3. GG got replaced with ABC.

Replace null Value

This, I will do a little bit differently to explore one more option. To replace the null value, I only selected one column and opted for “replace value” option. replace null → n/a. null should be all small caps.

I got

Only one column is replaced, pay attention to the code, it can take a list of columns and it has one column “Item”, as of now. I modified it manually to add “Item Cat”.

= Table.ReplaceValue(#"Replaced Value",null,"n/a",Replacer.ReplaceValue,{"Item", "Item Cat"})

Now I got

Now for both columns null is replaced with n/a

Replace with Average

To replace null values with an average value, I have taken the below data. I used the fill sheet from PivotData.xlsx shared on GitHub. I copy pasted data in Power BI (Enter Data UI)

Power BI Replace with Average Value

As there is no option to replace null with an average value under replace values option, I have selected the option to replace the null values with 0 for the “Maths” column.

Once done, I right-clicked on the query and opened Advance editor and I got.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY47EsAgCESvkqG2MDNRtMw/OYPj/a8RCK5Js7C8RSyFZnKUvYjWLDJ6dZmpukKLAbAPrB1w6lXBZi5xq++agh1ZXLNFRQemdltdZ6c0UxCJ4fespBVeg/Qh4g6+0uiNUWQkTATXBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Maths = _t, Physics = _t, Chemistry = _t, Computers = _t, English = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Maths", Int64.Type}, {"Physics", Int64.Type}, {"Chemistry", Int64.Type}, {"Computers", Int64.Type}, {"English", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Maths"})
in
    #"Replaced Value"

We are interested in the last step #”Replaced Value”

#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Maths"})

To get the average of the column we can use List.Average. But that needs Table[Column] not just the column name. As we need the current table column, we will use the table name in the previous step. So the table plus column name is #”Changed Type”[Maths]

We replace 0 with List.Average(#”Changed Type”[Maths])

The new code is

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY47EsAgCESvkqG2MDNRtMw/OYPj/a8RCK5Js7C8RSyFZnKUvYjWLDJ6dZmpukKLAbAPrB1w6lXBZi5xq++agh1ZXLNFRQemdltdZ6c0UxCJ4fespBVeg/Qh4g6+0uiNUWQkTATXBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Maths = _t, Physics = _t, Chemistry = _t, Computers = _t, English = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Maths", Int64.Type}, {"Physics", Int64.Type}, {"Chemistry", Int64.Type}, {"Computers", Int64.Type}, {"English", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,List.Average(#"Changed Type"[Maths]),Replacer.ReplaceValue,{"Maths"})
in
    #"Replaced Value"

Check step #”Replaced Value” above.

And we got

Power BI Replace Value with Average

Click Here to access all my blogs and videos in a jiffy via an exclusive visual glossary using Power BI.
Please like, share, and comment on these. Your suggestions on improvement, challenges, and new topics will help me explore more.

You Can watch my Power BI Tutorial Series on My Channel, Subscribe, Like, and share