I am using the Power BI AWS Athena Connector with Simba Athena ODBC driver to import data in Power BI Desktop. This works fine as long as I don't change the data types. I have a text column which I want to convert to an integer. The column contains some dashes ('-') though, so before I change the type of the column, I filter out the rows with the dash. In Athena itself this works fine, on other databases it works fine. In Power Query with Athena as source, however, I get the below message:
"OLE DB or ODBC error: [DataSource.Error] ODBC: ERROR [HY000] [Simba] [ Athena] (1040) An error has been thrown from the AWS Athena client. Error Message: INVALID_CAST_ARGUMENT: Cannot cast '000000000-' to DOUBLE ..."
I don't want Athena to do the casting, I would rather let Power Query do it after importing the data. I am then sure the correct order is used. How can I make sure the conversion is done in Power Query and not pushed back to Athena?
