Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe 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.
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.
Solved! Go to 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.
Proud to be a 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.
Proud to be a Super User! |
|
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
84 | |
69 | |
68 | |
39 | |
37 |