Replace value based on condition from other column

[goodhvnting]

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 🙂 

[bharath_v · Accepted solution]

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 · Accepted solution]

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"

[bolfri · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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.

[goodhvnting]

This worked as well! Thank you!