Forum Discussion

nniphadk's avatar
nniphadk
Frequent Visitor
4 years ago
Solved

Change value in Column A based on values in Column B

Column A contains list of string/work = Hot, Medium, or Cold.

Column B contains list of unique ID's.

IF value in Column B ("674186,142577,1017332,485806,138425,1040448,826341,1006619,138927,142008,1126308,429107,337550,132521,337625,123625,756117,737068,1064902,1028046"))
Change the value in Column A = 'Hot'

 

 

Need Help
Show and Tell
Tutorial Requests
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, nniphadk 

     

    You can use the DAX function to solve this problem.

    Column A = 
IF (
    [Column B]
        IN {
        674186,142577,1017332,
        485806,138425,1040448,826341,
        1006619,138927,142008,1126308,
        429107,337550,132521,337625,
        123625,756117,737068,1064902,1028046
    },
    "Hot",
    BLANK ()
)

    Is this the output you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, nniphadk 

     

    You can use the DAX function to solve this problem.

    Column A = 
IF (
    [Column B]
        IN {
        674186,142577,1017332,
        485806,138425,1040448,826341,
        1006619,138927,142008,1126308,
        429107,337550,132521,337625,
        123625,756117,737068,1064902,1028046
    },
    "Hot",
    BLANK ()
)

    Is this the output you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • tackytechtom's avatar
    tackytechtom
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    Hi nniphadk ,

     

    I think I have a solution in Power Query:

     

    Before

     

     

    After:

     

    Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sgvUdJRMjM3MbQwU4rViVbyTU3JLM0FihmaGJmam4PFnPNzUkAiBobmxsZGYCGIPkMwQNVnBAbI+ozBQCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Column A],each if List.Contains({674186,142577,1017332,485806,138425,1040448,826341,1006619,138927,142008,1126308,429107,337550,132521,337625,123625,756117,737068,1064902,1028046}, [Column B]) then "Hot" else [Column A],Replacer.ReplaceValue,{"Column A"})

in
    #"Replaced Value"

     

    Let me know if this works out for you 🙂

     

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/