Tricky Power Query M replacement

[Anonymous]

Hi all, 

I am struggling finding the best way to manage the following. In a Sales table I have the following columns:

[Client_Descr]

[Nation_Calc]

[Nation_Dest]

I need to replace column [Nation_Dest] with "GERMANY" when column [Client_Descr] contains "Ster" AND column [Nation_Calc] is "IRELAND"

I tried multiple versions of Table.ReplaceValue but didn't find a way to achieve the expected result.

Can someone please help?

Many thanks,

C.

[ronrsnfld]

Source

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxS0lHyDHL1cfRzAbKKUnMKlWJ1opWSU1DEHZ2cXcDiwSWpRSgyqWnpGWCZikoU8cys7BxkHUATgGRuXn4BdnMigEApNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client_Descr = _t, Nation_Calc = _t, Nation_Dest = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Client_Descr", type text}, {"Nation_Calc", type text}, {"Nation_Dest", type text}}),
    
    #"Replace with GERMANY" = Table.ReplaceValue(
        #"Changed Type",
        each [Client_Descr],
        each [Nation_Calc],
        (x,y,z) as text=>if y = "Ster" and z = "IRELAND" then "GERMANY" else x,
        {"Nation_Dest"}
    )
in
    #"Replace with GERMANY"

Results

[Oswaldo_J]

= Table.ReplaceValue(Origen, each [Nation_Dest],each if ([Client_Descr]="Ster" and [Nation_Calc]="Ireland" ) then "GERMANY" else [Nation_Dest], Replacer.ReplaceValue,{"Nation_Dest"})

[Ahmedx]

pls try this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZDBCoJAEIZfRfbsIYK6bxaS1BqtEWUephpicd2VLRN9+laDiLkN//cz8zF5zsQqYiE7KlOqUvkpdrapgykrwpzF8MIWOp8ezlGaZakgBa4VmhYc+jyBW3lpJpPpPLEGn6QprRn2nMEBJVA9G/MYJFB1KKmEhAc6n633qw0XS2pQ13q4frKNC6TqkfDIVjXoQc9W9HKk8W19JlK55TGB379kqrqi02Du1Oo1Si2gK8H92Gxke+hHuONCiX9WfAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Client_Descr = _t, Nation_Calc = _t, Nation_Dest = _t]),
    from = Table.TransformColumnTypes(Source,{{"Client_Descr", type text}, {"Nation_Calc", type text}, {"Nation_Dest", type text}}),
    Custom1 = [
a = List.Contains( from[Client_Descr],"Ster") and List.Contains(from[Nation_Calc],"IRELAND"),
b = Table.RenameColumns(from,{{"Nation_Dest", "GERMANY"}}),
c = if a then b else from][c] 

in
    Custom1