Replace value in each row based on text value of another column

[Anonymous]

Hello,

I have the following table in Power Query and i am trying to add a step to update the values in Paid amount - Original and Paid amount - Fixed columns based on the following conditions, for each row:

1. If the Invoice value is "No", then change the Paid amount - Original and Paid amount - Fixed values to negative.

2. If the Invoice value is "Yes", then no change.

Any help is much appreciated!

Document ID	Invoice	Paid amount - Original	Paid amount - Fixed	Update Date	Process Status	Status Code
3998	Yes	0	4619531.28	03/01/2022	Processed On Time	1
4050	Yes	0	17852.4	02/01/2023	Processed On Time	1
4050	Yes	6949.15	6949.15	09/01/2023	Not Processed	3
4060	No	757.16	757.16	10/01/2022	Not Processed	3
4139	Yes	4598	4598	17/01/2022	Not Processed	3
4145	Yes	142854.11	142854.11	16/01/2023	Not Processed	3
4149	No	0	104557.06	24/01/2022	Processed Late	2
4157	No	42827	42827	31/01/2022	Not Processed	3
4160	Yes	393.12	393.12	07/02/2022	Not Processed	3
4160	Yes	2840.2	2840.2	14/02/2022	Not Processed	3
4164	Yes	1220	1220	21/02/2022	Not Processed	3
4166	Yes	20600	20600	28/02/2022	Not Processed	3
4187	Yes	13423.85	13423.85	07/03/2022	Not Processed	3
4187	Yes	522.89	522.89	14/03/2022	Processed On Time	1
4187	Yes	467.69	467.69	21/03/2022	Processed On Time	1
4187	Yes	120	120	28/03/2022	Processed On Time	1
4248	Yes	636	636	04/04/2022	Processed On Time	1
4314	Yes	800	800	11/04/2022	Processed On Time	1
4474	Yes	1660	1660	18/04/2022	Not Processed	3
4481	Yes	208936	208936	25/04/2022	Processed On Time	1
4487	Yes	1292	1292	02/05/2022	Processed On Time	1
4495	Yes	1292	1292	09/05/2022	Processed On Time	1
4685	Yes	542.05	542.05	16/05/2022	Processed On Time	1
4688	Yes	23595	23595	23/05/2022	Not Processed	3
4691	Yes	916861.32	916861.32	30/05/2022	Not Processed	3
4813	Yes	185581.42	185581.42	06/06/2022	Processed On Time	1
4832	Yes	515503.82	515503.82	13/06/2022	Processed On Time	1
4997	Yes	766120.66	766120.66	20/06/2022	Processed On Time	1

[v-yanjiang-msft]

Hi @Anonymous ,

According to your description, here's my solution.

Table.ReplaceValue(#"Changed Type", each [#"Paid amount - Original"], each if [Invoice]="Yes" then [#"Paid amount - Original"] else[#"Paid amount - Original"]*-1, Replacer.ReplaceValue,{"Paid amount - Original"})

Here's the whole code of my sample, you can copy-paste it in a blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZTNblshEIVfxbrriMw/M+8QJVlkU1lZVK0XXbSRYr+/AuTaYKuW8cLM4VrzwZwBttuFI3x5WH7s9mWE8hPDUMZE9TPwI+AjAVGZvH5+/Nrt97vfm5d/m7c/f3flGy7vD9tFQOEMgtmVktQZrQSeJ1hIJNQzBTFwnj8OmxOrzHllGLQ/y5A1J7RRIAylXAEgx2kTos2YNWCeyBY9ZaOQqyTES223y0CJYxnNSxAtRUAtguS//Xj6eahW0pqv+ZhfVqY8RMaJMqx3goMT0iigGEF3EMgFEo0CZYYg3Uoi6IFwJtv6+uVQwBh9It9zX52FOLmey+oC30FRouQxiuoCT9yskSKWk8Uoqhv3UnC18+jGTD5JfySM7TRCqUIm8hl7P7314XtEnMuXPJwHa8frGHwgXOmEOA7nwaNtvQud3MOZh0E91DdOZwih1wgxRzDvBBVKoKOoj8scpXeTWNuuehwYV/y06H4GmhsmpgvNcJvjyN0PV3VMQhcaSk02UZO3VVdnUBXKTaULjTzHiuidzmblqqT2ooya4Dbr/Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Document ID" = _t, Invoice = _t, #"Paid amount - Original" = _t, #"Paid amount - Fixed" = _t, #"Update Date" = _t, #"Process Status" = _t, #"Status Code" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Document ID", Int64.Type}, {"Invoice", type text}, {"Paid amount - Original", type number}, {"Paid amount - Fixed", type number}, {"Update Date", type text}, {"Process Status", type text}, {"Status Code", Int64.Type}}),
    #"New"= Table.ReplaceValue(#"Changed Type", each [#"Paid amount - Original"], each if [Invoice]="Yes" then [#"Paid amount - Original"] else[#"Paid amount - Original"]*-1, Replacer.ReplaceValue,{"Paid amount - Original"})
in
    #"New"

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

@v-yanjiang-msft Thanks for your reply! Is it possible to edit the code to allow both the Paid amount - Original and Paid amount - Fixed values to change based on the Invoice text?

Here is my attempt but it is not working:

= Table.ReplaceValue(#"Changed Type", each [#"Paid amount - Original"] && [#"Paid amount - Fixed"], each if [Invoice]="Yes" then [#"Paid amount - Original"] && [#"Paid amount - Fixed"] else[#"Paid amount - Original"]*-1 && [#"Paid amount - Fixed"]*-1, Replacer.ReplaceValue,{"Paid amount - Original", "Paid amount - Fixed"})

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, you can do it in two steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZTNblshEIVfxbrriMw/M+8QJVlkU1lZVK0XXbSRYr+/AuTaYKuW8cLM4VrzwZwBttuFI3x5WH7s9mWE8hPDUMZE9TPwI+AjAVGZvH5+/Nrt97vfm5d/m7c/f3flGy7vD9tFQOEMgtmVktQZrQSeJ1hIJNQzBTFwnj8OmxOrzHllGLQ/y5A1J7RRIAylXAEgx2kTos2YNWCeyBY9ZaOQqyTES223y0CJYxnNSxAtRUAtguS//Xj6eahW0pqv+ZhfVqY8RMaJMqx3goMT0iigGEF3EMgFEo0CZYYg3Uoi6IFwJtv6+uVQwBh9It9zX52FOLmey+oC30FRouQxiuoCT9yskSKWk8Uoqhv3UnC18+jGTD5JfySM7TRCqUIm8hl7P7314XtEnMuXPJwHa8frGHwgXOmEOA7nwaNtvQud3MOZh0E91DdOZwih1wgxRzDvBBVKoKOoj8scpXeTWNuuehwYV/y06H4GmhsmpgvNcJvjyN0PV3VMQhcaSk02UZO3VVdnUBXKTaULjTzHiuidzmblqqT2ooya4Dbr/Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Document ID" = _t, Invoice = _t, #"Paid amount - Original" = _t, #"Paid amount - Fixed" = _t, #"Update Date" = _t, #"Process Status" = _t, #"Status Code" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Document ID", Int64.Type}, {"Invoice", type text}, {"Paid amount - Original", type number}, {"Paid amount - Fixed", type number}, {"Update Date", type text}, {"Process Status", type text}, {"Status Code", Int64.Type}}),
    #"ReplaceValue"= Table.ReplaceValue(#"Changed Type", each [#"Paid amount - Original"], each if [Invoice]="Yes" then [#"Paid amount - Original"] else[#"Paid amount - Original"]*-1, Replacer.ReplaceValue,{"Paid amount - Original"}),
    #"ReplaceValue2"=Table.ReplaceValue(#"ReplaceValue", each [#"Paid amount - Fixed"], each if [Invoice]="Yes" then [#"Paid amount - Fixed"] else[#"Paid amount - Fixed"]*-1, Replacer.ReplaceValue,{"Paid amount - Fixed"})
in
    #"ReplaceValue2"

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[Chew_WenJie]

Hello, you may create a conditional column which
                    IF Invoice = "No" then -1 else if Invoice = "Yes" then 1.

after that just use the Paid amount - Original and Paid amount - Fixed values multiply with the conditional column.

hope this can help you.

[Anonymous]

@Chew_WenJie Thanks for your reply!

I was hoping for one or more steps to fix the Paid amount - Original and Paid amount - Fixed values without duplicating these columns. Is that possible?

[Chew_WenJie]

Hihi, you can multiply the Paid amount - Original & Paid amount - Fixed and delete the orginal column and replace the name to the new column, if you wish to make modifications to the column and it cannot be transform by one step, you must create a new column.

You may wait for other power bi specialists to come up with a better method for making the calculation.

Hope this helpful to you.

[Anonymous]

I tried this step but it is giving an error:

= Table.ReplaceValue(#"Changed Type", each [Paid amount - Original], each if [Invoice] = "No" then -[Paid amount - Original] else [Paid amount - Original], Replacer.ReplaceValue, {"Paid amount - Original"})

Is it possible to do the changes for both columns in the same step?