Forum Discussion

goodhvnting's avatar
goodhvnting
Frequent Visitor
3 years ago
Solved

Replace value based on condition from other column

Hello! 

 

I've searched this forum to try to find a solution to my problem but haven't quite gotten there yet so I am reaching out to the community. 

 

I am trying to replace a value in a column based on a condition from another column. I can't do this by creating a conditional column because there are hundreds of values and it would take too long for me to specify for each one when really I only want to change one value. 

 

This is the statement I have so far but it is not achieving my desired result: 

= Table.ReplaceValue(#"Reordered Columns", each [Campground_Name],each if [PARK_ID] = "88" then "Chain Lakes South" else [Campground_Name], Replacer.ReplaceValue,{"CAMPGROUND_NAME"})

 

Basically, what I want is for every time [Park_ID] = 88 then change [Campground_Name] to "Chain Lakes South". 

 

Any help would be greatly appreciated 🙂 

 

 

Need Help
  • bharath_v's avatar
    bharath_v
    3 years ago

    Hi goodhvnting 

     

    I believe you can still use conditional column to define one value and use "Else" clause for rest of the values.

    I tried to simulate your scenario, let me know if this helps!

    Input:

     

    Conditional Column:

    Output:

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

  • bolfri's avatar
    bolfri
    3 years ago

    You almost did it. 🙂

     

    = Table.ReplaceValue(
                #"Changed Type",
                each if [PARK_ID] = 88 then [Campground_Name] else false,
                each "Chain Lakes South",
                Replacer.ReplaceValue,
                {"Campground_Name"}
            )

     

    Before:

    After:

    Full code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrOz01VyM9JUSguKU1LU4rViVYywi5sjF3YwgK7uKUlCepjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PARK_ID = _t, Campground_Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PARK_ID", Int64.Type}, {"Campground_Name", type text}}),
    #"Replaced Value" 
        = Table.ReplaceValue(
                #"Changed Type",
                each if [PARK_ID] = 88 then [Campground_Name] else false,
                each "Chain Lakes South",
                Replacer.ReplaceValue,
                {"Campground_Name"}
            )
in
    #"Replaced Value"
  • goodhvnting's avatar
    goodhvnting
    3 years ago

    This worked perfectly. Thank you so much! I knew I was close but wouldn't have been able to get there without the help. 

     

    Cheers! 

5 Replies

  • bolfri's avatar
    bolfri
    Solution Sage
    3 years ago

    You almost did it. 🙂

     

    = Table.ReplaceValue(
                #"Changed Type",
                each if [PARK_ID] = 88 then [Campground_Name] else false,
                each "Chain Lakes South",
                Replacer.ReplaceValue,
                {"Campground_Name"}
            )

     

    Before:

    After:

    Full code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrOz01VyM9JUSguKU1LU4rViVYywi5sjF3YwgK7uKUlCepjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PARK_ID = _t, Campground_Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PARK_ID", Int64.Type}, {"Campground_Name", type text}}),
    #"Replaced Value" 
        = Table.ReplaceValue(
                #"Changed Type",
                each if [PARK_ID] = 88 then [Campground_Name] else false,
                each "Chain Lakes South",
                Replacer.ReplaceValue,
                {"Campground_Name"}
            )
in
    #"Replaced Value"
    • goodhvnting's avatar
      goodhvnting
      Frequent Visitor
      3 years ago

      This worked perfectly. Thank you so much! I knew I was close but wouldn't have been able to get there without the help. 

       

      Cheers! 

  • bharath_v's avatar
    bharath_v
    Resolver I
    3 years ago

    Hi goodhvnting 

     

    I believe you can still use conditional column to define one value and use "Else" clause for rest of the values.

    I tried to simulate your scenario, let me know if this helps!

    Input:

     

    Conditional Column:

    Output:

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