Forum Discussion
How to replace a value based on multiple conditions
HI davidoz,
You can try to add a custom column with the below if statement expressions to do replace value operations:
#"Added Custom" = Table.AddColumn(#"Changed Type","Replace", each if [PHASE_NAME] = "Flowback" and [TYPE] = "Separators > 100-285psi Storage Separator" then if [JOB] = "DJ-229" then "ST-0033" else if [JOB] = "DJ-50" then "ST-0014" else if [JOB] = "DJ-85" then "ST-0098" else [NAME] else [NAME] )Full query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvJL09KTM5W0lEKTi1ILEosyS8qVogpNTAwTlUwNDDQNbIwLSjOVAgGiiempyrAFQE1uHjpGhlZAhk+wbpA9cZKsToUG2hqADPPxIQa5lmYwswzNKSGeUaGVPawhakhsgtjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PHASE_NAME = _t, TYPE = _t, JOB = _t, NAME = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"PHASE_NAME", type text}, {"TYPE", type text}, {"JOB", type text}, {"NAME", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type","Replace", each if [PHASE_NAME] = "Flowback" and [TYPE] = "Separators > 100-285psi Storage Separator" then if [JOB] = "DJ-229" then "ST-0033" else if [JOB] = "DJ-50" then "ST-0014" else if [JOB] = "DJ-85" then "ST-0098" else [NAME] else [NAME] ) in #"Added Custom"Regards,
Xiaoxin Sheng
so the issue with the "create a new column" solutions is that I need to run this 3 or 4 times. And I also only need to change part of the name, not the entire name.
For example:
If [JOB] = "DJ-229" AND [PHASE_NAME] = "Flowback" AND [TYPE] = "Separators > 100-285psi Storage Separator" THEN change "LS-0033" in [NAME] to "ST-0033"
If [JOB] = "DJ-50" AND [PHASE_NAME] = "Flowback" AND [TYPE] = "Separators > 100-285psi Storage Separator" THEN change "LS-0044" in [NAME] to "ST-0014"
If [JOB] = "DJ-85" AND [PHASE_NAME] = "Flowback" AND [TYPE] = "Separators > 100-285psi Storage Separator" THEN change "LS-0011" in [NAME] to "ST-0098"
Any ideas?
HI davidoz,
You can try to add a custom column with the below if statement expressions to do replace value operations:
#"Added Custom" =
Table.AddColumn(#"Changed Type","Replace",
each
if
[PHASE_NAME] = "Flowback" and [TYPE] = "Separators > 100-285psi Storage Separator"
then
if [JOB] = "DJ-229" then "ST-0033"
else
if [JOB] = "DJ-50" then "ST-0014"
else
if [JOB] = "DJ-85" then "ST-0098"
else
[NAME]
else
[NAME]
)
Full query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvJL09KTM5W0lEKTi1ILEosyS8qVogpNTAwTlUwNDDQNbIwLSjOVAgGiiempyrAFQE1uHjpGhlZAhk+wbpA9cZKsToUG2hqADPPxIQa5lmYwswzNKSGeUaGVPawhakhsgtjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PHASE_NAME = _t, TYPE = _t, JOB = _t, NAME = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PHASE_NAME", type text}, {"TYPE", type text}, {"JOB", type text}, {"NAME", type text}}),
#"Added Custom" =
Table.AddColumn(#"Changed Type","Replace",
each
if
[PHASE_NAME] = "Flowback" and [TYPE] = "Separators > 100-285psi Storage Separator"
then
if [JOB] = "DJ-229" then "ST-0033"
else
if [JOB] = "DJ-50" then "ST-0014"
else
if [JOB] = "DJ-85" then "ST-0098"
else
[NAME]
else
[NAME]
)
in
#"Added Custom"Regards,
Xiaoxin Sheng
Thank you. This is the solution I went with. It wasn't Ideal having to make a custom column but I couldn't find a way around that