Expressions that yield variant data type cannot be used

[DouweMeer]

I created a logic to combine a product description from multiple excel files. At one point it started giving me a data variant error so I was looking for numbers in my expression. I was unable to find the number so I tried something 'silly'. The result was unexpected:

Trying to improve, I tried the following, which 'worked' as in that the error was gone. 

Curious that this one is accepted in a text column

This is accepted

When placing the "1" on any of the other position, the error returns and sticks. But when you double on one of the VAR's, the issue is gone. 

Removing the single "1" and replacing it by a blank() does not return the error. Also, replacing the "1" by just 1, does solve the problem as well. 

I have no clue how to solve it. 

[v-yingjl]

Hi @DouweMeer ,

Based on your description, perhaps you can try to put the a6 variable in the last swtich() statement to get the result:

Column =
VAR a1 =
    BLANK ()
VAR a2 =
    BLANK ()
VAR a3 =
    BLANK ()
VAR a4 =
    BLANK ()
VAR a5 =
    BLANK ()
VAR a6 = "1"
RETURN
    SWITCH (
        TRUE (),
        a5 <> BLANK (), a5,
        a2 <> BLANK (), a2,
        a4 <> BLANK (), a4,
        a1 <> BLANK (), a1,
        a3 <> BLANK (), a3,
        a6 <> BLANK (), a6,
        BLANK ()
    )

Best Regards,
Yingjie Li

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

[DouweMeer]

@v-yingjl 

Strange that this removes the behavior. Although extremely unfavourable as this expression is meant to swtich between product tabels dependable on priority. In some fields some of the sources do not have a related data field and therefore a blank is entered in the VAR. If I would change the position for each field to make sure that the last VAR is always filled in, it becomes a mess to write, maintain, and check on it. 

I'll report a ticket to make sure they fix this. It shouldn't be like this. 

[amitchandak]

@DouweMeer , With data type text and blank(), it should not give this. Try to move a6 as first and try.

I tried this and it worked

Measure = VAR A1 = blank() 
VAR A2= blank() 
VAR A3 = blank() 
VAR A4 = 4
return switch(True() , 
A1<> BLANK(),A1 ,
A2<> BLANK(),A2 ,
A3<> BLANK(),A3 ,
A4<> BLANK(),A4 
)

[DouweMeer]

@amitchandak 

I assume you meant it like below. If so, that doesn't work either.