PowerQuery changing a column value based on information in another column

[trevb]

I find it easy enough to change the value of a column I am working on but I want to be able to work my way down the "table" changing the value of one column depending to a value from another column if the value in a third column is a particular value.

i.e.

ID       Name      Value1     Value2    Status    Report          

1         A             10            100         Good      10

2         B             10            100         Good      10

3         C             10            100         Bad        100

4         D             10            100         Good      10

5         E             10            100         Good      10

So I would want Report to use Value2 only where status is bad, otherwise it uses Value1.  This is not what I'm actually trying to do but should explain what I need to achieve without too much complexity.  If I can do this then I can add the complexity to achieve what I am after.

Anyone got any ideas?

[Greg_Deckler · Accepted solution]

@trevb - In Power Query (M) your formula for Report would be:

if [Status] = "Bad" then [Value2] else [Value1]