Forum Discussion
Replace values
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 t...
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 repValuePete
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