replace one column value based on other column criteria

Hi Doro ,

 

How about this:

There are two ways of doing it:

a) You replace every value in [Goods], use this code

= Table.ReplaceValue(#"Changed Type",each [Goods], each if [Quantity] > 0 and not Text.StartsWith([Goods], "d") then "d11"  else [Goods],Replacer.ReplaceText,{"Goods"})

 

The code in advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY5LDoAgDAXv0jULSvE0hIUobjQxhkSvLx/TGsJu8jp9rXOAoCCAVw50pgsZH6bEtEpmKxpdY9maB+5muzG1LcPxzRRsd5a0Ka7Eof+htaWBEP7XcCBEqY2VcKLiEsen4MG0f27hJff6Fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quantity = _t, Goods = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Quantity", Int64.Type}, {"Goods", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [Goods], each if [Quantity] > 0 and not Text.StartsWith([Goods], "d") then "d11"  else [Goods],Replacer.ReplaceText,{"Goods"})
in
    #"Replaced Value"

 

b) Create a custom column, drop the old column and rename the new one to Goods. The custom column uses this code bit:

if [Quantity] > 0 and not Text.StartsWith([Goods], "d") then "d11"  else [Goods]

 

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("bY5LDoAgDAXv0jULSvE0hIUobjQxhkSvLx/TGsJu8jp9rXOAoCCAVw50pgsZH6bEtEpmKxpdY9maB+5muzG1LcPxzRRsd5a0Ka7Eof+htaWBEP7XcCBEqY2VcKLiEsen4MG0f27hJff6Fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quantity = _t, Goods = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Quantity", Int64.Type}, {"Goods", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Quantity] > 0 and not Text.StartsWith([Goods], "d") then "d11"  else [Goods]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Goods"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Goods"}})
in
    #"Renamed Columns"

 

Does this one work for you? 🙂 

 

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom