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

[snph1777]

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?

[parry2k]

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

[snph1777]

Thanks parry2k, will get back

[timg]

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

[snph1777]

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

[snph1777]

@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

[snph1777]

Thanks timg, will get back