I'm new to power query, and I'm using the latest Excel (beta) release on Mac.

I currently have the following step:

Table.ReplaceValue(#"Replaced Value2","payment_v2","-1",Replacer.ReplaceText,{"type - Copy"})

It works to replace all cells of the column where the value is "payment_v2" into "-1".

Good.

Now I want to add a condition to this formula. I'd like to first check if the column "payee" is "X", and if it's X I want to replace the value by "1", not "-1". But if it's not X, then I want to replace the value by "-1" as intended by the step above.

I've tried to look up conditional statements in power query, and I've attempted a few things, but nothing works. I can detail what I tried and what the errors were if requested, but my question is really "how to handle an if statement as part of that step?".

Thank you

CodePudding user response：

If you want to add another column with the 1 for X and -1 for anything else, based on the same table and column as you used for your original replace, you can use this:

Table.AddColumn(#"Replaced Value2", "Custom", each if [#"type-Copy"] = "X" then 1 else -1)

Or if you just want to update the original table and column you could try this instead:

Table.TransformColumns(#"Replaced Value2", {"type-Copy", each if Text.Upper(_) = "X" then "1" else "-1"})

It transforms each cell in the type-Copy column based on whether the current entry is an X or not.

CodePudding user response：

Try

= Table.ReplaceValue((#"Replaced Value2", each [#"type - Copy"], each if [#"type - Copy"] = "payment_v2" and [payee]="X" then "1" else if [#"type - Copy"] = "payment_v2" then "-1" else [#"type - Copy"], Replacer.ReplaceText, {"type - Copy"})