Forum Discussion

snph1777's avatar
snph1777
Helper V
5 years ago

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:

 

 

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?

Need Help
Tips and Tricks
Tutorial Requests

6 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    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.⚡

    • timg's avatar
      timg
      Solution Sage
      5 years ago

      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.

      11

       

      Hope it helps!

       

      Best regards,

      Tim

      2