Replace values

[Anonymous]

Hi, 
I have two columns - countries and target. I want to change target to 1 if the country column include name of specific countries.
Look at the example. In this example I want to change Target to 1 for Italy and France. Is it possible to do this without creating additional column?

Country	Traget
23456France	3
3456Italy	4
98765Spain	5
45678Greece	7
9872France	4
098France	7
0834Germany	4
0987Italy	9
34567Italy	8
9876Spain	0
3468098Spain	5
23098Spain	4
9798Poland	7

Thanks for help,
Aleks

[BA_Pete · Accepted solution]

Hi Aleks,

Select your [Traget] column, right-click on the column header and select 'Replace Values'.

In the dialog, enter any number to find and to replace with and hit ok. I chose to find 9999 and replace with 1111, so the code produced in the formula bar looks like this:

Now you can edit the code to make the search dynamic by changing it to this:

Full example query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY69DsIwDAbfxXOHKH92XoCKDYkx6mBBBqQSUMTStyckjVXW0332xQjaWOdPhfMtwQQGlinCD50/vG6V2EYCoXfXNz9yRa6h6iDNJaU2xKFpudWXKpCQLikydk7lyXk7Wjg+BmkQRBIxGtQuearT/zBtDmjPx0CX18r53iuWLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Traget = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Traget", Int64.Type}}),

    repValue = Table.ReplaceValue(chgTypes, each [Traget], each if Text.Contains([Country], "Italy") or Text.Contains([Country], "France") then 1 else [Traget], Replacer.ReplaceValue, {"Traget"})

in
    repValue

Pete