Replace single value in power query in power bi desktop cellwise without having to rely on excel

[Anonymous]

in power bi desktop in power query,i want to replace a single value "NA" in a column by 67 but all the NA values get replaced by 67 which i do not want.So suggest me workaround to replace NA values seperately or replace a single NA value leaving all others NAs unchanged.

I want to replace the values when working with my projects without having to rely on Excel which supports cell specific replacement 

[Anonymous]

I have a query named 2003[2] in power query.there are many values including NA andN/A values in the column Loss(mW) column.

I want to replace the NA value corresponding to Poor Line Maintenance by the average of the values corresponfing to PoorLine Maintenance.(below screenshot ) I had to filter my Loss Column with the NA and N/A values. But when I try to replace the value correponding to "Poor Line Maintenance",the average for the (Loss column) values(below screenshot) is coming 9830.38 something approximately. I tried to replace the NA value corrsponding to Poor Line Maintenance, I got 9830.38 in all the  NA values. All the NA values get replaced which I do not want.I want to rreplace single NA values based on other column values in the  query data .
I want to replace each NA value with corresponding to "Type of Disturbance" column. Secondly filtering the data once also restricts my data and it is difficult to revert back to the original view - which shows all the values in my Loss column.
I tried to calculate the average of all values in excel by copying the below screenshot in excel.

Once I filter by Poor Line Maintenance,the power query adds another step in the query. For viewing purposes, I want to be comfortable with view without having to filter. I do not want to calculate average by copy-pasting the above second screenshot in excel and the find out average.
I need help. If possible some dynamic m-code will be an added help.

[slorin]

Hi

Replace "NA" by 67 in [Column1]   if [Column test] = "Your Test" 

= Table.ReplaceValue(
   prev_Step,
   each [Column test]="Your Test" and [Column1]="NA",
   each [Column1],
   (row,test,if_false) => if test then 67 else if_false,
   {"Column1"}
)

 Stéphane

[DOLEARY85]

Hi,

You could create another column and pull through all the values of the original column but change the value for the single N/A using some other fields that have a unique value to that row.

If you can provide a sample of the data i'd be able to provide some more detailed help.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍