Forum Discussion

ianwuk's avatar
ianwuk
Helper III
8 years ago

How to manually replace one value in a table?

Hello.

 

I have an orders table that looks like this:

 

ORDERID   AMOUNT

          1          100

          2           159

          3           400

          4            543

 

How can I change it so that in the AMOUNT column, for ORDERID 2 the AMOUNT is changed to 60?

 

I tried creating a custom column that looked like this:

 

(IF [ORDERID] = 2 then AMOUNT = 60 ELSE AMOUNT = AMOUNT)

 

This did not work and returned boolean values in the custom column.

 

Ideally I just want to change it in the AMOUNT column directly for the specific row. How can that be done?  

 

The reason being is that the AMOUNT value is used for other custom columns in the same table and creating an additional custom column of the AMOUNT column would make it confusing.

 

I saw the thread here on this but I did not understand it.  It seems to be what I want to do, specifically the DAX formula part to replace the value directly.  But I do not understand how to do it.

https://community.powerbi.com/t5/Desktop/Replacing-just-1-value-in-a-table/m-p/75871/highlight/true#M31594


Can someone please help me edit just one value?  The replace values option replaces all values.  Not just single ones.

Thanks.

ianwuk

2 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee

    Hi ianwuk,

    One way is to create a calculated column using DAX in Power BI desktop interface. Please use the formula below and get expected New_AMOUNT column.

    New_AMOUNT = IF(Table1[ORDERID]=2,60,Table1[AMOUNT])




    Another way is to create a custom column in Query Editor using Power Query, click add column->custom column, type the formula.

    =if [ORDERID]=2 then 60 else [AMOUNT]

    type formularesult
    Best Regards,
    Angelia