Type mismatch - Unable to convert a value to the data type requested

[neetu14]

Getting datatype mismatch error when refreshing the report on Power BI Service.

datatype in SQL source table is nvarchar whereas in power bi desktop it is a decimal number (which is what I need for the report)

And I do not have permission to change datatype in the source table.

Is there a way to somehow create a new column with desired data type and copy values from existing source column to new one and let power bi desktop convert the data type before publishing the report?

Processing error:The current operation was cancelled because another operation in the transaction failed. The following system error occurred: Type mismatch. Unable to convert a value to the data type requested for table 'AAS_Processing' column 'Avg_ProcessingTimes'. The data provider was unable to convert the value '<pii>NaN</pii>' from the source data type 'VT_BSTR' to the expected data type 'VT_R8'. Please check the column data types in the data model and data source and ensure that the data types are compatible.

[CN1]

Hi there 🙂

I have the same issue, but I'm not finding the "wrong value" to replace.

Erro de processamento: The following system error occurred:  Type mismatch.  Unable to convert a value to the data type requested for table 'Metas 2022' column 'Mês do Ano'. The data provider was unable to convert the value '' from the source data type 'VT_BSTR' to the expected data type 'VT_DATE'. Please check the column data types in the data model and data source and ensure that the data types are compatible.

What's the value '' ?

There's no blank values on table 😕 can you help please?

There are the related tables on desktop:

And there's the source of "Metas 2022":

Can you help?

Thank you.

[Greg_Deckler]

Can you leave it as text in the query and then in the Desktop (not Query Editor), change the Data Type of the column?

[neetu14]

I tried that and published the report, got the same error.

Is there any way to convert the data type in desktop instead of sql source?

[Greg_Deckler]

Wait, I read the error message a little better. Try filtering out "<pii>NaN</pii>" in that column in Query Editor. It looks like there are some rows that have "<pii>NaN</pii>" in them for that column perhaps? It won't be able to convert that to a number so you will have to filter those out. Do this before any Change Type in Power Query where you try to change it to a decimal number.

[neetu14]

Thanks for your reply. Is there a way to replace the 'NAN' value with 0 for future records?

For example, what if the data being refreshed tomorrow has the same value, can it be automatically replaced with 0 and report is refreshed without any issues.

[Greg_Deckler · Accepted solution]

Sure, right-click the colum and use Replace Values. In Query Editor.