Forum Discussion
Replacing Values below a certain Number
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.
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.
1 Reply
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.