replace one column value based on other column criteria

[Doro]

Hello, need help with my data how can I replace all cels to "d11" value in column Goods that have positive Quantity and starts not from "d"? Thank you in advance!

Quantity	Goods
1	b
0	q1
0	w
0	s
0	d
0	s4
200	d1
0	a
0	d
0	f4
0	a
300	d2
0	v
0	b4
0	s
302	d4
0	b
0	s4
300	s
0	b
0	b
300	d1
0	b
0	e4
0	e
153	d3
0	o3
0	l
0	k
150	c1

[tackytechtom · Accepted solution]

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

[Doro]

Hello again , and what if i would like to replace all that not starts from "d" and "d1"? 

[v-jingzhang]

Hi @Doro 

Please share some sample data and expected result based on your new requirement. I think values that don't start from "d" already include values that don't start from "d1". And what is the new value to replace them? Do we need to take positive Quantity into account?

Best Regards,
Community Support Team _ Jing

[Doro]

Hello, hope you ingood mood and health, well basicaly sample of data same as in top of post, but task slightly diffrent, replace all cels to "d11" value in column `Goods` that have positive `Quantity` and starts not from "d" and  "d1"...

[v-jingzhang]

Hi @Doro 

I think the current solution already meets your need. Goods that don't start from "d" include goods that don't start from "d1". So you don't need to modify it. Based on the sample data, only values in the highlighted rows below need to be replaced, right? 

Regards,

Jing

[Doro]

Thank you for quick reply and your rime, work flawless!