Forum Discussion

ktruong2's avatar
ktruong2
Regular Visitor
8 years ago
Solved

Query Editor - Transformation - Replace values: Text starts with

Hi,

 

Is there a way to replace values (000) for only text begins with (T000) in Query Editor?

 

Example:

 

Part Numbers:

T000-0851

ABCabc

EFGT000

 

Desired Outcome:

 

Part Numbers:

000-0851

ABCabc

EFGT000

 

My issue now is when I use the "Replace Values" I'm afraid that it'll replace all T000 with 000.

  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    ktruong2

     

    To avoid replacing the values which you want to keep, we can add an IF() condition in the expression.

     

    Please refer: 

    New Text =
IF (
    LEFT ( Table3[Text], 4 ) = "T000",
    REPLACE ( Table3[Text], 1, 4, "000" ),
    Table3[Text]
)

    Thanks,

    Xi Jin.

1 Reply

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Solution Sage
    8 years ago

    ktruong2

     

    To avoid replacing the values which you want to keep, we can add an IF() condition in the expression.

     

    Please refer: 

    New Text =
IF (
    LEFT ( Table3[Text], 4 ) = "T000",
    REPLACE ( Table3[Text], 1, 4, "000" ),
    Table3[Text]
)

    Thanks,

    Xi Jin.