Nested IF Statement Query using PowerQuery not giving all the desired results

[JorgeMartinez92]

Hi all, 

I am building a file which will update itself almost automatically thanks to queries in PowerQuery. I am almost at and end of the process where the results should be displayed as this:

• If:
  1. SIO Status is Active and the CC is 3130005 then Action is Bill.
  2. SIO Status is End Dated and the CC is 3130005 then Action is Bill Manually.
  3. SIO Status is No SIO and the CC is 3130005 then Action is Create SIO.
  4. SIO Status is Active, or End Dated, or No SIO and the CC first two characters start with 31 then Action is Reclass to Correct CC.
  5. SIO Status is Active, or End Dated, or No SIO and the CC first two characters don't start with 31 then Action is Check.
  6. Else Action is Check.

This is the IF Statement I have for the above conditions:

= Table.AddColumn(#"Filtered Rows", "Actions", each if [SIO Status] = "Active" and [Cost Centre ID] = "3130005" then "Bill" else if [SIO Status] = "End Dated" and [Cost Centre ID] = "3130005" then "Bill manually and validate if extension is required" else if [SIO Status] = "No SIO" and [Cost Centre ID] = "3130005" then "Create SIO" else if [SIO Status] = "Active" or [SIO Status] = "End Dated" or [SIO Status] = "No SIO" and Text.Start([Cost Centre ID],2)="31" then "Reclass to correct CC" else if [SIO Status] = "Active" or [SIO Status] = "End Dated" or [SIO Status] = "No SIO" and Text.Start([Cost Centre ID],2) <> "31" then "Check" else "Check")

It is doing the job for all except for two conditions in the last IF:

1. SIO Status is Active, or End Dated, or No SIO and the CC first two characters don't start with 31 then Action is Check.

For Active and End Dated is showing the Action Reclass to correct CC which is not correct. It should be Check. Here is the current result for that line of the nested IF statement:

SIO Status	Cost Centre ID	Actions
Active	101274	Reclass to correct CC
End Dated	2300018	Reclass to correct CC
End Dated	1110457	Reclass to correct CC
No SIO	1110201	Check

I was wondering if someone could point out what is going wrong as the last line of the nested IF works well for one of the conditions but ignores the other two. I assume it is getting mixed up with the previous line of the nested IF but I do not know how to avoid this.

Suggestions on how to make this formula look more elegant and trimmed are more than welcome.

[HotChilli]

It's not about being new to power Query. You know some maths I'm sure.  

There are some problems you just have to work through.

I have confidence in you.

[JorgeMartinez92]

Spare the patronizing comments if you don't intend to answer something as simple as to which type of brackets you are referring to.

[HotChilli]

 '[' are not the right brackets

[JorgeMartinez92]

Failry new to PowerQuery, perhaps you meant these brackets then {} ? 

[HotChilli]

It looks like an issue caused by and/or not behaving in the way you want.

You can put brackets round the

[SIO Status] = "Active" or [SIO Status] = "End Dated" or [SIO Status] = "No SIO"

 to make the logic work

[JorgeMartinez92]

Thanks for the suggestion, however when I edit the lines to add the brackets:

[[SIO Status] = "Active" or [SIO Status] = "End Dated" or [SIO Status] = "No SIO"] and Text.Start([Cost Centre ID],2)="31" then "Reclass to correct CC" else if [[SIO Status] = "Active" or [SIO Status] = "End Dated" or [SIO Status] = "No SIO"] and Text.Start([Cost Centre ID],2) <> "31" then "Check" else "Check")

I then get the Token Comma Expected error highligthing the "t" in Active.