Forum Discussion
yield variant data-type error - Glitch?
Hi DouweMeer ,
There's no official explanation for this in the documentation at the moment, but I've done some testing of my own and here are my results:
I've found that your reported error occurs when, and only when, the last determination condition returns BLANK() (non-others) and the data type of the content returned by the preceding determination condition is either text or boolean:But if the last determination condition is not BLANK(), then this error is not reported:
Or the last judgment condition returns BLANK() but the previous judgment condition returns a number or date (date/time) type, no error will be reported, but if the previous judgment condition returns a date (date/time) type, the computed column will ultimately be determined to be of number type:
Based on the above test results, I have summarized the following two patterns for myself:
1. Because BLANK() itself is not of any data type, when there are multiple judgment conditions, the BLANK() function determines the data type of BLANK() based on the data type of the content returned by the judgment condition that follows the one it is in.
2. If BLANK() is in the last judgment condition (non-others), then it will be interpreted preferentially as a number type. This also explains the error if the content you previously returned was of type text.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
👉Power BI needs to know what type of data to expect from your SWITCH statement. If some results return text while others return BLANK(), it can throw an error. Make sure all outcomes of your SWITCH statement return the same data type.
When returning empty values, use BLANK() instead of "" (empty string). The latter is treated as text, while BLANK() is a distinct data type. If you have other results that return text, using BLANK() can lead to type mismatches.
You can also temporarily add VAR statements to store intermediate results for easier debugging.
Ensure that each boolean expression is valid and that it’s not returning unexpected results that could lead to errors.
Perhaps you misunderstood. "" (empty string), the one you propose to avoid, was the only solution as blank() was not accepted.
I know, it doesn't make sense.
Hi DouweMeer ,
There's no official explanation for this in the documentation at the moment, but I've done some testing of my own and here are my results:
I've found that your reported error occurs when, and only when, the last determination condition returns BLANK() (non-others) and the data type of the content returned by the preceding determination condition is either text or boolean:But if the last determination condition is not BLANK(), then this error is not reported:
Or the last judgment condition returns BLANK() but the previous judgment condition returns a number or date (date/time) type, no error will be reported, but if the previous judgment condition returns a date (date/time) type, the computed column will ultimately be determined to be of number type:
Based on the above test results, I have summarized the following two patterns for myself:
1. Because BLANK() itself is not of any data type, when there are multiple judgment conditions, the BLANK() function determines the data type of BLANK() based on the data type of the content returned by the judgment condition that follows the one it is in.
2. If BLANK() is in the last judgment condition (non-others), then it will be interpreted preferentially as a number type. This also explains the error if the content you previously returned was of type text.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.