Power Query : Replace value with 0 based on criteria name "Mercy"

[muhammad susanto]

hi all.

how replace value with 0 in col Amount based on col. Model
i want replace value for Mercy with 0
like this table

Book1
	A	B	C	D	E	F	G	H	I
1	original data					after using Power Query
2	Region	Model	Date	Amount		Region	Model	Date	Amount
3	Hongkong	BMW	01/03/2022	10		Hongkong	BMW	01/03/2022	10
4	Hongkong	BMW	01/04/2022	10		Hongkong	BMW	01/04/2022	10
5	Hongkong	BMW	01/05/2022	10		Hongkong	BMW	01/05/2022	10
6	Hongkong	BMW	01/06/2022	10		Hongkong	BMW	01/06/2022	10
7	Hongkong	BMW	01/07/2022	10		Hongkong	BMW	01/07/2022	10
8	Hongkong	BMW	01/08/2022	10		Hongkong	BMW	01/08/2022	10
9	Hongkong	BMW	01/09/2022	10		Hongkong	BMW	01/09/2022	10
10	Hongkong	BMW	01/10/2022	5		Hongkong	BMW	01/10/2022	5
11	Hongkong	BMW	01/11/2022	4		Hongkong	BMW	01/11/2022	4
12	Hongkong	BMW	01/12/2022	5		Hongkong	BMW	01/12/2022	5
13	Malay	Mercy	01/03/2022	11		Malay	Mercy	01/03/2022	0
14	Malay	Mercy	01/04/2022	12		Malay	Mercy	01/04/2022	0
15	Malay	Mercy	01/05/2022	13		Malay	Mercy	01/05/2022	0
16	Malay	Mercy	01/06/2022	20		Malay	Mercy	01/06/2022	0
17	Malay	Mercy	01/07/2022	20		Malay	Mercy	01/07/2022	0
18	Malay	Mercy	01/08/2022	20		Malay	Mercy	01/08/2022	0
19	Malay	Mercy	01/09/2022	20		Malay	Mercy	01/09/2022	0
20	Malay	Mercy	01/10/2022	0		Malay	Mercy	01/10/2022	0
21	Malay	Mercy	01/11/2022	0		Malay	Mercy	01/11/2022	0
22	Malay	Mercy	01/12/2022	0		Malay	Mercy	01/12/2022	0
Sheet1

thanks in advance

susant

 

[Automatrix]

New conditional column: If Model equals Mercy then 0 else column Amount. Then delete old Amount column and rename the new column.

 

[muhammad susanto]

hi..
still not work, in col Amount the all result is 0

 

[Automatrix]

Can I see a screenshot of your conditions?

 

[muhammad susanto]

hi Automatrix...
like my sample in column I "Amount" Model "BMW" after using PQ, should be contains numbers from adjacent column
here screenshot after using your formula

 

[Automatrix]

hi Automatrix...
like my sample in column I "Amount" Model "BMW" after using PQ, should be contains numbers from adjacent column
here screenshot after using your formula

Change the dropdown under the word "Else" from "Enter a value" to "Select a column". Then select the Amount column.

 

[GraH]

You can actually achieve this inside the column.

Table.ReplaceValue(PreviousStep, each [Amount], each if [Model] = "Mercy" then 0 else [Amount],Replacer.ReplaceValue, {"Amount"})

I'm without Excel so I hope I got all of the syntax right.