Divide IF value is over a certain amount

[elliswoods]

n

[v-karpurapud]

Hi @elliswoods 

We have not received a response from you regarding the query and were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you for your understanding and participation.

[v-karpurapud]

Hi @elliswoods 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation.

[v-karpurapud]

Hi @elliswoods 

Could you please confirm if your query have been resolved the solution provided by @rohit1991 & @slorin ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently. If not , kindly share the detailed information regarding the issue.

Thank you

[rohit1991]

Hi @elliswoods ,

There's no direct DIVIDEIF function in Power Query, but you can easily achieve this using a conditional column or a simple custom column with an if statement. Here's a quick workaround:

In Power Query, add a Custom Column with this formula:

= Table.TransformColumns(
    YourPreviousStep,
    {{"Amount", each if _ > 100 then _ / 52.143 else _}}
)

Replace YourColumnName with your actual column name. This will divide the value by 52.143 only if it's over 100, and leave it unchanged otherwise. You can then remove or replace the original column as needed.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.

Connect with me on LinkedIn: Rohit Kumar.

[rohit1991]

Hi @elliswoods ,

To divide values in a column only if they are above a certain threshold (e.g., 100), you can use Power Query's if...then...else logic within a custom column. In this case, you'll want to check if the value is greater than 100, and if so, divide it by 52.143; otherwise, leave it as-is. This can be done using the "Add Column" → "Custom Column" option in Power Query. The expression evaluates each row individually and applies the logic accordingly.

Here's the Power Query M code you can use in the Custom Column dialog:

if [YourColumnName] > 100 then [YourColumnName] / 52.143 else [YourColumnName]

Just replace YourColumnName with the actual name of your column. This will create a new column with the adjusted weekly values where necessary, while keeping the existing ones unchanged if they’re already correct.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.

Connect with me on LinkedIn: Rohit Kumar.

[slorin]

It's hard to answer without knowing your data and previous steps

Stéphane

[slorin]

Hi @elliswoods 

= Table.TransformColumns(Your_Source,{{"Your_Column", 
      each if _< 100 or _ = null or _ = "" then _ else _/52.143, type number}})

Stéphane