IF statement

[Cyrilbrd]

Good day,

Given the following 

Location	Type	Expected result
shop1	OS	ecom
shop1	SS	shop1
shop1	ES	emp
shop2	OS	ecom
shop2	SS	shop2
shop3	SS	shop3
shop4	OS	ecom

I tried Add Column Custom Column but can't use a nested IF statement as it returns Expression Error  The name 'IF' wasn't recognized. Make sure it's spelled correctly.
I tried to use IF THEN ELSE but I am not sure about the syntax.
Thanks for the heads-up.

[Cyrilbrd]

Thank you to both for clarifying the syntax of IF THEN ELSE.

@mussaenda why did you use Text.Upper(), does it mean that this IF version is case sensitive?

[mussaenda]

Hi @Cyrilbrd ,

Are you able to solve your issue?

[v-yiruan-msft · Accepted solution]

Hi @Cyrilbrd ,

Text.Upper() function is used to convert all characters inside Type column to uppercase. If there is mixed case or lowercase values in the type column in the field, such as eS or es, it can avoid the return value of false when your condition is set to [Type]="ES"...

In addition to using an IF statement just as suggested by @mussaenda and @Jihwan_Kim to get the result you want, you can also create a calculated column by using a SWITCH statement to get it.

Column = SWITCH ( 'Table'[Type], "ES", "emp", "OS", "ecom", [Location] )

Best Regards

[Jihwan_Kim]

[mussaenda]

Hi @Cyrilbrd ,

Are you trying with DAX?

IF(
UPPER([Type]) = "OS",
"ecom",

IF(
UPPER([Type]) = "ES",
"emp",

IF(
UPPER([Type]) = "SS",
[Location],

[Location]
)
)
)

In Power Query

if Text.Upper([Type]) = "OS"
then "ecom"
else

if Text.Upper([Type]) = "ES"
then "emp"
else

if Text.Upper([Type]) = "SS"
then [Location]
else

[Location]

Assuming you have no null values on both columns used on the statement.

Hope this helps