Replacing Values below a certain Number

[JamesBurke]

HI all , 

I have a usages column as well as an Dim Time Id and a Dim Date ID , 

= Table.AddColumn(#"Changed Type", "Replace Values", each if [Usages KWH] < 1 then 1 else if [Usages KWH] > 1 then 0 else null)

I want to essentially say when Replace Values = 1 Use Hourly Average When replace Values = 2 Use Total consumption , which would replace values below 1 with an average to replace bad data. 

Thanks , James. 

[bhanu_gautam · Accepted solution]

@JamesBurke , Add a Custom Column to determine the "Replace Values" based on the conditions you specified.
Add another Custom Column to replace the values based on the "Replace Values" column.

In the Power Query Editor, go to the Add Column tab and click on Custom Column.
Enter the following formula in the Custom Column dialog box:

= if [Usages KWH] < 1 then 1 else if [Usages KWH] > 1 then 0 else null

Name this column "Replace Values" and click OK.

Again, go to the Add Column tab and click on Custom Column.
Enter the following formula in the Custom Column dialog box:

= if [Replace Values] = 1 then [Hourly Average] else if [Replace Values] = 0 then [Total Consumption] else [Usages KWH]

Name this column "Adjusted Usages KWH" and click OK.