Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JorgeMartinez92
Frequent Visitor

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

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 StatusCost Centre IDActions
Active101274Reclass to correct CC
End Dated2300018Reclass to correct CC
End Dated1110457Reclass to correct CC
No SIO1110201Check

 

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.

6 REPLIES 6
HotChilli
Super User
Super User

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.

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
Super User
Super User

 '[' are not the right brackets

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

HotChilli
Super User
Super User

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

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors