PowerQuery if with multiple conditions AND OR

[chrisbatson75]

Should be simple

Research = if(or('Forced Receipts'[Cost Amt]>500, 'Forced Receipts'[OC Stat]="Closed","Required",""))

Doesnt work.

I want that it the Forced Receipts cost amount is mroe than $500 to print required in the field 

OF

if the OC Stat is = to closed, then print required.

What i am missing in the formual?

[SteveCampbell · Accepted solution]

Hello,

When you say PowerQuery, are you writing this in the PowerQuery editor in the M language, or in DAX?

By the looks of your code it appears you are adding a column in DAX (PowerQuery is the query editor).

You are missing a closing bracket in the OR statement after "Closed":

Research =
IF (
    OR ( 'Forced Receipts'[Cost Amt] > 500, 'Forced Receipts'[OC Stat] = "Closed" ),
    "Required",
    ""
)

I would reccomend using the double bar || as an OR. You can nest multple OR statements (The AND equivilent is && ). Also, you can use BLANK() instead of "":

Research =
IF (
    'Forced Receipts'[Cost Amt] > 500
        || 'Forced Receipts'[OC Stat] = "Closed",
    "Required",
    BLANK()
)

If you are in PowerQuery, it is somwhat different. In the M language, the advanced editor could look like this:

Table.AddColumn(///Last Step///, "Research", each if [Cost Amt] >500 or [OC Stat] = "Closed" then "Required" else null)

relace

///Last Step///

with the name of your last step. Alternatively, you can use add conditional column.