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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
snph1777
Helper V
Helper V

Power BI Service - schedule refresh error - Type mismatch - data type 'VT_BSTR' to 'VT_I8'

This question is more related to Power BI Desktop, even though it is actually in Power BI Service (scheduled refresh).

 

I have a Power BI Desktop file called ABC.pbix.

It works fine, and am able to publish it online to Power BI Service.

However, when I schedule the refresh in Power BI Service, I get the error (sent via email) posted in the image below:

 

pbix dtype.GIF

 

I have a table called X, with a column called Y in the Power BI file. The table X has its source from an Excel sheet.

 

Column Y has some blanks, zeroes, and integer values, and the data type set for Column Y in the Power BI Desktop file is Whole Number.

 

Column Y is also used in the computation of a Calculated Column called Z, which has logic with multiple conditions (using SWITCH statement),

 

Z = SWITCH (TRUE(),
                       X[Y] > 30, 25
                       X[Y] < 30, 14
                       .....)

 

I even tried to develop the Calculated Column Z, using the CONVERT function:

 

Z = SWITCH (TRUE(),
                      CONVERT(X[Y], DOUBLE) > 30, 25
                      CONVERT(X[Y], DOUBLE) < 30, 14
                       .....)

 

The error still persists. (I am not too familiar with the Power BI Query Editor. Should I change something there ?) What should I do to fix this issue during the scheduled refresh?

6 REPLIES 6
parry2k
Super User
Super User

@snph1777 can you make sure col Y doesn't have any text value, seems like your source has some text values in it, although you changed the type to the whole number if there is a text, it is going to throw an error. I will first manually check at the source.

 

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks parry2k, will get back

Hi snph,

You could start out by checking whether any imported records contain an error when they are imported as an integer. you can do this by enabling the column quality setting in power query (image 1). After this, you can enable "profiling based on entire dataset" to make sure the column quality takes into account all data you are about to refresh (image 2). If the "error" row in the column quality area shows that there are indeed errors this means your column does not only contain integer data types. 

If it isn't a data issue, the PBI documentation shows that the cause could also be an out of date Power bi desktop version. So it might be worth checking whether you are working with the latest version of PBI desktop as well.

1111

 

Hope it helps!

 

Best regards,

Tim

22





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@timg     i have posted some information. Any idea to proceed ?

@timg

 

 

The source excel has 8 zeroes and 1672 blanks, and many (thousands of) values that are integers, for Column Y in table X.

 

When loaded into Power BI Desktop (Query Editor) , I find something odd:

319 blank values
1361 NULLs
and the integer values stay as they are.

 

 

 I don't know what the reason is for this deviation. The zeroes from excel disappear inside the Power BI Query Editor. Also a dispropotionate number of blank values. What the NULLs inside the Power BI Query Editor mean? They were not even present in Excel.

 

The tally in both cases come to 1680 + the proper integer values

Thanks timg, will get back

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.