after data refresh all data types turned into Text

[Anonymous]

Hi, 

I took over a dashboard (.pbix) file from a collaborator. First thing I wanted to do is refresh the data (through an Athena ODBC connection) using the refresh button in my Power BI desktop environment. All data types where set from the Data View Mode and before refreshing everything looks fine e.g. number of decimales, % types, etc. 

After refreshing most visuals were broken with Error Message: 'Something's wrong with one or more fields:...'. While checking Data Types all previously set were truned into Text when viewing in Data View > Modeling. 

Any ideas suggestions?

Cheers, 

Maarten

[lbieber]

This randomly happens to us.  A column will say "Whole Number," then we click Refresh, and things break because the column is now type Text.  We have not been able to determine the cause.  Of course, we checked the data type in the source database as well.  Any ideas how to avoid this in the future?

[amitchandak]

@Anonymous , You need to figure out why data type has changed.

Open Data Transformation and Edit Query mode.  Change Data type there. In case there is some error look for the error row and correct it.

refer to this for that https://www.youtube.com/watch?v=9-Lag0VOiTs

[Anonymous]

@AllisonKennedy  and @amitchandak Thanks for the quick replies! 

After refresh I can change data types without any errors occuring (but there several tables and many columns:))! Actually another collaborator can refresh without errors. Can it be that there are Settings that introduce this behaviour? I checked File> Options > Local File > Data Load with and without automatic Data Type detection but both same error.

[AllisonKennedy]

@Anonymous are you and collaborator both using the same version of the report and the same version of power bi desktop?

Can you please confirm that you are changing data types using M in Power Query, not just in the Data View? Using M is a more reliable way to change the data type and will force the conversion to number even if there is text, but will return an error.

[AllisonKennedy]

I would recommend setting the data type in the Query Editor, NOT in the data view:
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-data-types

That will ensure that data type is set properly upon refresh.

If you're still having trouble with the refresh or there are errors in the Query Editor, please share your M code from Advanced Editor if able