0

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

Improve this question
1
  • There are numerous references on the internet and also on SO for replacing a value in a column based on another column. If those don't help, please add data sample that can be copy/pasted, as well as what you have tried even if it hasn't worked. Please take a look at How to create a Minimal, Complete, and Verifiable example. Commented Apr 26, 2022 at 10:03

2 Answers 2

Reset to default
1

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"})
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

1 Comment

@Horseyride's answer is a better (more specific) match to your question than mine is.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.