none
Power Query if Statement to replace a cell's value? RRS feed

  • Question

  • Hi There,

    I would like to replace the value of two cells in a record in Power Query.

    The row has the following data: the field column name is CCT (PK) with the value DFA11-000001. The fields which need to be replaced in this record are Annuity and Once Off with a new value. the current value x needs to be removed. Each of these have an x value which needs to be replaced with 10000 and 50000.

    Thanks for your assistance.

    Regards,

    Kevin



    Thursday, June 9, 2016 12:49 PM

Answers

  • You need to create two new columns and use IF statements to determine their values. If I understand correctly, the annuity value should be 10000 if the Circuit Number is equal to DFA11-0000001

    New custom column helperAnnuity using the formula

    if [Circuit Number] = "DFA11-0000001" then 10000 else [Annuity]

    New column HelperOnceOff using the formula

    if [Circuit Number] = "DFA11-0000001" then 50000 else [Once Off]

    Then delete the  Annuity and Once Off columns and rename HelperAnnuity and HeperOnceOff to Annuity and Once Off respectively. 


    cheers, teylyn

    Friday, June 10, 2016 8:04 AM

All replies

  • Hi Kevin. Can you share a brief sample of a few rows of the input table, and what the desired output would be? I'm not exactly sure what you're trying to do.

    Thanks,

    Ehren

    Thursday, June 9, 2016 5:52 PM
    Owner
  • Hi Ehren,

    Thanks for your response.

    I have tried to load an image which would make it much clearer but apparently my account has not been verified to do so. Tried requesting verification on t a thread but no luck.

    As requested, see the source data and output below.

    I hope this helps...

    Source Data
    Client Name Circuit Number Created Date Annuity Once Off
    SpeedX DFA12-0003169 2016-03-31 680 200
    NetNetworks DFA11-0000001 2016-04-12 680 200
    FFT DFA12-0003202 2016-04-19 680 200
    Output
    Client Name Circuit Number Created Date Annuity Once Off
    SpeedX DFA12-0003169 2016-03-31 680 200
    NetNetworks DFA11-0000001 2016-04-12 10000 50000
    FFT DFA12-0003202 2016-04-19 680 200

    Thanks for your time.

    Kevin

    Friday, June 10, 2016 6:51 AM
  • You need to create two new columns and use IF statements to determine their values. If I understand correctly, the annuity value should be 10000 if the Circuit Number is equal to DFA11-0000001

    New custom column helperAnnuity using the formula

    if [Circuit Number] = "DFA11-0000001" then 10000 else [Annuity]

    New column HelperOnceOff using the formula

    if [Circuit Number] = "DFA11-0000001" then 50000 else [Once Off]

    Then delete the  Annuity and Once Off columns and rename HelperAnnuity and HeperOnceOff to Annuity and Once Off respectively. 


    cheers, teylyn

    Friday, June 10, 2016 8:04 AM
  • Yes! Awesome! Thank you Teylyn, appreciate it.
    Friday, June 10, 2016 8:18 AM