Custom Column with isblank and isnotblank

[hayleypnch]

Hi All, 

I'm looking at creating a custom column based on the contents of 2 other columns. I want to say:

If column 1 and column 2 are both blank, display "outcome 1" in the column

If column 1 is not blank and column 2 is blank, display "Outcome 2" in the column

If Column 2 is not blank, display "Outcome 3" in the column.

I have written this:

if(ISBLANK [Column1] and ISBLANK[Colmun2], "Outcome1",
if(ISNOTBLANK [Column1] and ISBLANK [Column2],"Outcome2",
if(ISNOTBLANK[Column2], "Outcome3" ))))

But I'm getting an error under the "Outcome1" section. Any ideas?

[Anonymous]

Hi @hayleypnch ,

•  Add a Custom column In Power Query

=if Text.Length([Column1])=0 and Text.Length([Column2])=0 then "Outcome1" else if Text.Length([Column1])>0 and Text.Length([Column2])=0 then "Outcome2" else "Outcome3"

• Use DAX to add a column:

New Column = 
SWITCH (
    TRUE (),
    [Column1] = BLANK ()
        && [Column2] = BLANK (), "Outcome1",
    [Column1] <> BLANK ()
        && [Column2] = BLANK (), "Outcome2",
    [Column2] <> BLANK (), "Outcome3"
)

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

Here is a column expression that should work.

Outcome = SWITCH(TRUE(),

Blanks[Column1] = "" && Blanks[Column2]="", "Outcome 1",

Blanks[Column2]="", "Outcome 2",

NOT(Blanks[Column2]=""), "Outcome 3")

Pat