How to replace values if condition is true in Power BI?

Let’s assume that your product team wants to categorize items based on their profit margin. Part of data pre-processing is to replace numerical values with descriptive labels. How can you implement this conditional value replacement to quickly identify high-performing and underperforming products?

In this tutorial we will use both Power Query and DAX to replace values based on conditions. Our ‘Products’ table has columns: ProductID (Number), ProductName (Text), Category (Text), and ProfitMargin (Percentage).

Change column values conditionally using Power Query

1. In Power BI Desktop, click ‘Transform data’ on the Home tab.
2. In Power Query Editor, go to ‘Add Column’ tab and click ‘Conditional Column’.
3. Set up the following rules:

• If ‘ProfitMargin’ is greater than or equal to 0.30, then the cell value will read “High Margin”
• Else if ‘ProfitMargin’ is greater than or equal to 0.15, then “Medium Margin”
• Else “Low Margin”

4. Name the new column “Margin Category” and click ‘OK’.

Alternatively, you can use Power Query M code to accomplish a similar result:

= Table.AddColumn(Products, "Margin Category", each 
    if [ProfitMargin] >= 0.30 then "High Margin"
    else if [ProfitMargin] >= 0.15 then "Medium Margin"
    else "Low Margin")

Replace Power BI values conditionally at the correct layer

Start with one reversible comparison and preserve the current state. Write the condition and expected output as a truth table. Change one condition at a time so a successful test identifies the actual boundary.

Check the deciding layer

Use Power Query for stored transformation and DAX for model-context output. Treat null, blank, zero, and error separately. Use a non-sensitive sample and the normal user account when possible. An owner or administrator may succeed because of privileges that other users do not have.

Preserve the original column. Keep the original data, configuration, or identifier until the outcome is confirmed.

Verify the ordinary workflow

Test boundary cases and refresh. Measure how many rows changed. Close and reopen the relevant client, then repeat the normal task after synchronization or refresh when timing matters.

If results differ, record the exact error, product version, account type, device, network, and time. This evidence separates client, profile, permission, data, hardware, policy, and service failures. Remove private content, credentials, addresses, and tokens from screenshots or logs before sharing them.

Have another authorized user repeat the shortest safe test when shared access is involved. Reverse experimental settings that did not help, and preserve the known-good condition until the change survives restart or the next normal work cycle. Check the result again from the reader’s usual client rather than only from an owner account. Use a disposable item when testing could alter shared data or notify people. Keep a concise rollback note for future updates or device replacement. Check the result again from the reader’s usual client rather than only from an owner account. Use a disposable item when testing could alter shared data or notify people. Keep a concise rollback note for future updates or device replacement. Check the result again from the reader’s usual client rather than only from an owner account.

Replace Power Bi data conditionally using DAX

An alternative option is to use DAX expressions. Difference being that the calculations are made later, as part of the data analysis process. This typically taxes the overall performance of our reporting system.

1. In Power BI Desktop, go to the ‘Data’ view.
2. Select the ‘Products’ table.
3. Click ‘New Column’ and enter the following DAX formula:

Margin Category = 
SWITCH(
    TRUE(),
    Products[ProfitMargin] >= 0.30, "High Margin",
    Products[ProfitMargin] >= 0.15, "Medium Margin",
    "Low Margin"
)

To use in the data in a Power BI visual:

1. Create a matrix visualization.
2. Add ‘Category’ to Rows and ‘Margin Category’ to Columns.
3. Add a count of ProductID to Values.

This will show the distribution of margin categories across product categories.

Troubleshooting: Ensure ProfitMargin is formatted as a percentage. For complex conditions, consider using nested IF() statements in DAX.