i need to replace some values in a column

[LINUS_MAASTOK]

Hello, first that all excuse me for my bad english ;( I need your help, im new with power BI

I have a table named LECTURA that contains a column named SMS. The column SMS have a lot of values, and i need to change the value of a part of it.

The values i need to change have the structure "Lecture order 123456789 discarted, order state 8" and only the number changes from row to row... I need to replace all of these values to "Discarted"

[COLOR=#333333][FONT='inherit']SMS
[/FONT][/COLOR][COLOR=#333333]"[/COLOR][COLOR=#333333][FONT=Verdana]234524354[/FONT][/COLOR][COLOR=#333333]"                                                                                "[/COLOR][COLOR=#333333][FONT=Verdana]Non Discarted[/FONT][/COLOR][COLOR=#333333]"[/COLOR]
[COLOR=#333333][FONT='inherit']"[FONT=Verdana]Lecture order 123456789 discarted, order state 8[/FONT]"        TO             "[FONT=Verdana]Discarted[/FONT]"[/FONT][/COLOR]
[COLOR=#333333][FONT='inherit']"[FONT=Verdana]Lecture order 836456789 discarted, order state 8[/FONT]"       --->            "[FONT=Verdana]Discarted[/FONT]"[/FONT][/COLOR]
[COLOR=#333333][FONT='inherit']"[FONT=Verdana]Lecture order 543453459 discarted, order state 8[/FONT]"                         "[FONT=Verdana]Discarted[/FONT]"
[/FONT][/COLOR][COLOR=#333333]"[/COLOR][COLOR=#333333][FONT=Verdana]98203498234[/FONT][/COLOR][COLOR=#333333]"                                                                            "[/COLOR][COLOR=#333333][FONT=Verdana]Non Discarted[/FONT][/COLOR][COLOR=#333333]"[/COLOR]

I try with this .... but i failed

[COLOR=#000000][FONT=Consolas]Columna = LECTURA_APORTADA.ReplaceValue([TXT_SMS];
        each
            if (Text.StartsWith([TXT_SMS];"Orden de lectura "))
        [/FONT][/COLOR][COLOR=#000000][FONT=Consolas]       if (Text.StartsWith([TXT_SMS];"Orden de lectura "))
               and (Text.EndsWith([TXT_SMS];" descartada, estado de la orden 8"))
            then "Descartada"
            else [TXT_SMS];
        Replacer.ReplaceValue; {"TXT_SMS"}
    )
[/FONT][/COLOR]

some ideas? ;( and (Text.EndsWith([TXT_SMS];" descartada, estado de la orden 8"))
then "Descartada"
else [TXT_SMS];
Replacer.ReplaceValue; {"TXT_SMS"}
)

[/CODE]

some ideas? ;(

 

[yky]

Use this formula. You may need to adjust/change 15 in the MID function.

=IF(ISERR(SEARCH("discarted",A1)<>0),A1,MID(A1,15,9))

 

[sandy666]

via PQ with M-code

[SIZE=1]    IF = Table.AddColumn(Type, "Custom", each if Text.Contains([SMS], "Lecture") then "Discarted" else "Non Discarted")[/SIZE]

 

[LINUS_MAASTOK]

thanks both... the 2 solutions work nwn <3

 

[sandy666]

Glad to help

Have a nice day