Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 to 1 for Italy and France. Is it possible to do this without creating additional column?

CountryTraget
23456France3
3456Italy4
98765Spain5
45678Greece7
9872France4
098France7
0834Germany4
0987Italy9
34567Italy8
9876Spain0
3468098Spain5
23098Spain4
9798Poland7


Thanks for help,
Aleks

Need Help
Show and Tell
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    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

1 Reply

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    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