Case or IF Statement in Power Query

[Sime]

Hi All,

Is it possible to use a CASE or IF statement in Power Query to query mulitple conditions?

I'm using the following IF Statement but it is not capturing the "Encrouching Budget" line.

Is therea better way to achieve the desired result?

if [budget_amountcost] = 0 then "No Budget" else if[jtd_LaborAmountCost_inclUnposted] + [jtd_GL610205AmountCost] / [budget_amountcost] < .85 then "Ontrack" else if [jtd_LaborAmountCost_inclUnposted] + [jtd_GL610205AmountCost] / [budget_amountcost] < 1.01 then "Encroaching Budget" else if [jtd_LaborAmountCost_inclUnposted] + [jtd_GL610205AmountCost] / [budget_amountcost] > 1.01 then "Over Budget" else null

Thank you.

[PhilipTreacy · Accepted solution]

Hi @Sime 

There's nothing wrong with the syntax of that code so without some sample data it's hard to say why it's not working as you expect.  You need to explain what result you are expecting.

Can you please provide sample data or your file?

The only thing I'd ask you to check at this stage is whether the calculations are being carried out as you expect. 

For example, is it

[jtd_LaborAmountCost_inclUnposted] + [jtd_GL610205AmountCost] / [budget_amountcost] 

or is it

( [jtd_LaborAmountCost_inclUnposted] + [jtd_GL610205AmountCost] ) / [budget_amountcost]

Regards

Phil

[Sime]

Thank you Philip.  The lack of parentheses was the cause of the issue.

Greatly appreciate your assistance.