OR statements

[ola7mat]

Appreciate if anyone can advise if there is an easier way to write this in power query?

if [Colour] = "Red" or [Colour] = "blue" then true else false

I.e. how or can I write something like so

if [Colour] = {"Red","Blue"} then true else false

 thanks, am quite a novice as you can imagine!

[ChrisMendoza · Accepted solution]

Hello @ola7mat,

Using https://www.powerquery.training/portfolio/replicate-power-pivots-switch-function/ as a guide:

(input) =>

let

values = {
    { "Blue", true },
    { "Red", true },
    { input, false }
},

Result = List.First ( List.Select ( values, each _{ 0 } = input ) ) { 1 }

in

Result

Add Custom Column:

= fnSwitch ( [Colours] )

Yields the following result:

[ChrisMendoza · Accepted solution]

Hello @ola7mat,

Using https://www.powerquery.training/portfolio/replicate-power-pivots-switch-function/ as a guide:

(input) =>

let

values = {
    { "Blue", true },
    { "Red", true },
    { input, false }
},

Result = List.First ( List.Select ( values, each _{ 0 } = input ) ) { 1 }

in

Result

Add Custom Column:

= fnSwitch ( [Colours] )

Yields the following result:

[ola7mat]

Thanks for this Chris and JoHo . I am after the correct way to write this in power query though.

Chris the solution does work however I have a number of formulas where i use this and so wont create lists for each one.

Do you know what the power query version of what JoHO wrote?

IF(Table[Colour] IN {"Red", "Blue"}, TRUE(), FALSE())

[ChrisMendoza · Accepted solution]

@ola7mat,

The function allows you not to have to rewrite the many formula's. Define it once and call it as necessary. Possibly share some more details about the data and your desired output(s).

[ola7mat]

thanks for this, have finally got my head round the swtich function very useful.

appreciate the help very much!

[JoHo_BI]

Hi Ola,

Not too sure about Power Query, but DAX is easy enough.

2 ways, you can either use the IN keyword, or double pipes '||'.

In your example, 

IF(Table[Colour] IN {"Red", "Blue"}, TRUE(), FALSE())

Or...

IF(Table[Colour] = "Red" || Table[Colour] = "Blue", TRUE(), FALSE())

Hope that helps!