Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
DouweMeer
Post Prodigy
Post Prodigy

yield variant data-type error - Glitch?

I sometimes have a working calculated columns where I have a lengthy switch statment. I occasionally have to update this logic and sometimes this column throws a weird error. Prior it throwing an error, I have text and blank answers. I've made 100% sure all posible outcomes are actual text or simply blank(). 

 

Today again, I'm hit with data-type error after I changed one of the text results into a blank() result. If I change the blank() to "", it will no longer throw the error. Which is weird, because there are plenty other results in the switch statement that result in a blank() value. 

 

 

Switch ( true()
 , boolean expression , blank()
 , boolean expression , "Text result 1"
 , boolean expression , "Text result 2"
 , blank()
 )

 

 

The above doesn't return an error. But after I modify the above to:

 

 

Switch ( true()
 , boolean expression , blank()
 , boolean expression , "Text result 1"
 , boolean expression , blank()
 , blank()
 )

 

 

It does return an error, which is weird.

So it is now:

 

Switch ( true()
 , boolean expression , blank()
 , boolean expression , "Text result 1"
 , boolean expression , ""
 , blank()
 )

 

Anyone had that before? 

1 ACCEPTED SOLUTION

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:

vjunyantmsft_0-1727683834529.png

vjunyantmsft_1-1727683849689.png

But if the last determination condition is not BLANK(), then this error is not reported:

vjunyantmsft_4-1727683961252.png
vjunyantmsft_2-1727683941868.png


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:

vjunyantmsft_5-1727684064754.png

vjunyantmsft_6-1727684199199.png


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.

View solution in original post

3 REPLIES 3
Kedar_Pande
Memorable Member
Memorable Member

👉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:

vjunyantmsft_0-1727683834529.png

vjunyantmsft_1-1727683849689.png

But if the last determination condition is not BLANK(), then this error is not reported:

vjunyantmsft_4-1727683961252.png
vjunyantmsft_2-1727683941868.png


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:

vjunyantmsft_5-1727684064754.png

vjunyantmsft_6-1727684199199.png


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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.