Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
JamesBurke
Helper III
Helper III

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. 

 

1 ACCEPTED SOLUTION
bhanu_gautam
Super User
Super User

@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.

 

 




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






View solution in original post

1 REPLY 1
bhanu_gautam
Super User
Super User

@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.

 

 




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.