AWS Athena connector executes Power Query transformations in wrong order

[sondek]

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?

[sondek]

Thank you Vijay_A_Verma, Table.StopFolding is exactly what I needed!

[Vijay_A_Verma · Accepted solution]

Issue Table.StopFolding command to stop the operation being pushed back to the source. Example is given below

https://docs.microsoft.com/en-us/powerquery-m/table-stopfolding

Note - It doesn mean that you will lose the benefit of Query folding in successive operations.

[Mahi_11]

Hi, what if the source is not a table instead its a query.
let
Source = Odbc.Query("dsn=DW", "SELECT * FROM Table_name WHERE fiscyear >= 2020 ")
in
Source