Forum Discussion
Error on service refresh but works on desktop. Dateformat problem?
Hello everyone,
I have 2 reports that connects to a SQL Server Database (Collate: Modern_Spanish_CI_AS) and shows different tables and graphs.
Both of them, works well on power bi desktop, refresh the data and shows it perfectly.
If I try to publish them to the power bi service, it publishes correctly, but once published if I try to refresh the data, some errors appear.
The errors are always referring to a Date field changing the affected table and the affected field:
- Error de procesamientoThe following system error occurred: Type mismatch. Unable to convert a value to the data type requested for table 'FACOportunitySL' column 'OportunityDate'. 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. Table: FACOportunitySL.
- URI de clústerWABI-WEST-EUROPE-E-PRIMARY-redirect.analysis.windows.net
- Identificador de actividad1247d221-5276-4c88-9b04-ae900f1c46f6
- Identificador de la solicituda80c4bca-630c-183e-5b04-fb6c422df4e8
OportunityDate is a datetime field on SQL Server.
None of the rows has this field empty '' as the error suggest.
On power query editor, is a text field.
On dataview of powerbi is converted to a Date field without problems.
If I try to convert it to a Date field on power query editor, errors appears due to the regional configuration I guess.
One example:
Abr (Spanish Abril) != Apr (English April)
The regional configuration on the power bi desktop is set to English (United States).
Changed to Spanish but the error doesn't solve and other errors appear like confusing dot and commas on number formatting.
I have already tried to delete the data and report from power bi service and upload again without success.
Also updated the power bi gateway to the latest version.
Some ideas?
Thanks in advance.
Finally after some time I have discovered where the problem is and how to solve it, but no why.
I have changed the way I access de data of the SQL Server. Instead of selecting the table manually, I have set the SQL Query directly on the advanced options of the connection like this:
I have just put all the fields and magically all of them has the correct data type even the Date fields.
Instead of having the Source and Navigation steps applied, now I am only have the Source step.
No more changes needed, no regional settings changed, formats or any other additional configuration.
I hope this "trick" works for someone else struggling on the same issue.
Thanks to Anonymous anyway for the help.
2 Replies
Finally after some time I have discovered where the problem is and how to solve it, but no why.
I have changed the way I access de data of the SQL Server. Instead of selecting the table manually, I have set the SQL Query directly on the advanced options of the connection like this:
I have just put all the fields and magically all of them has the correct data type even the Date fields.
Instead of having the Source and Navigation steps applied, now I am only have the Source step.
No more changes needed, no regional settings changed, formats or any other additional configuration.
I hope this "trick" works for someone else struggling on the same issue.
Thanks to Anonymous anyway for the help.
Hi jruizairlan ,
To have PBI recognize those as dates Go to File => Options and settings => Options => Locale => change to your country/region. After setting, please restart Desktop ,then change data type in power query.
If the date field comes from a hidden date table, be careful when the model language is consistent.
If that doesn't work, you can split the date column in the Power Query editor, then change the data type to Date by replacing "Apr" with "4" (Replace from letters to numbers, and other months as well), then merge the columns sequentially into the Date column, and then change the column data type to Date.
You can refer to the following documents that may be helpful to you:
How to change the date format? - Microsoft Power BI Community
Date format - Microsoft Power BI Community
Manage Date Formats in the Power BI Service (mssqltips.com)
Change date format from Swedish to English in Powe... - Microsoft Power BI Community
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
