ODBC error for data source AWS Athena

[visheshjain]

Hello everyone,

I have a AWS Athena as my source for the data and I am getting the following error in PBI Desktop.

Everything works alright until I change the data type to whole number on one of my text columns.

If anyone has an idea about how to resolve this, please respond to this message.

Thank you,

[v-pnaroju-msft]

Hi visheshjain,

We wanted to see if the information we gave helped fix your problem. If you need more help, please feel free to contact the Microsoft Fabric community.

Thank you.

[visheshjain · Accepted solution]

The suggested solutions did not work and also look like they were copied from generative AI models.

At the moment the ODBC connection is quite slow and even after updating the connector to v2 it only slowed down the refresh time.

I am going to try to move the data source to a Fabric Lakehouse or Azure SQL to see if the refreshes take lesser time.

Thank you,

[v-pnaroju-msft]

Hi visheshjain,

We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Thankyou, @jaineshp, for your response.

Hi visheshjain,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

We would like to inquire whether have you got the chance to check the solution provided by @jaineshp to resolve the issue. We hope the information provided helps to clear the query.
Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[jaineshp]

Hey @visheshjain

You're absolutely right about query folding being crucial for performance! Let me suggest some approaches that maintain query folding while fixing the Athena data type conversion issue:

Query Folding-Friendly Solutions:

• Fix at the Athena source level - The most efficient approach:
  • Use CAST(column_name AS DOUBLE) or TRY_CAST(column_name AS DOUBLE) directly in your Athena SQL query
  • This ensures the conversion happens at the database level and maintains folding
    
    
• Modify the M query to use proper Athena syntax:
  • Instead of Power Query's type conversion, use: = Sql.Database("your-athena-connection", "SELECT CAST(your_column AS DOUBLE) FROM your_table")
  • This forces Athena to handle the conversion natively
• Use Value.NativeQuery with Athena-compatible syntax:
  
  = Value.NativeQuery(YourSource, "SELECT CAST(text_column AS DOUBLE) as numeric_column FROM your_table")
  
  Check your Athena table definition - Ensure the column is properly defined as DOUBLE/DECIMAL in the CREATE TABLE statement rather than STRING/VARCHAR

The key is letting Athena do the heavy lifting rather than Power BI. These approaches maintain query folding because the type conversion becomes part of the SQL that gets pushed down to Athena, rather than being processed locally in Power BI.

Try the direct CAST approach in your source query first - it should give you both the performance you need and resolve the conversion error.

Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

[jaineshp]

Hey @visheshjain,

Looking at your Athena connection error, this typically happens when Power BI tries to push data type conversions to the source database. Here are some quick solutions:

Try these steps:

• Don't change data type in Power Query - Instead, let Athena handle the original text format and convert it using DAX measures when needed
• Use Transform Data approach - Go to Transform Data → Add Custom Column → use Number.FromText([YourColumn]) instead of changing the column type directly
• Check your Athena table - Make sure the source column doesn't have any non-numeric values, nulls, or special characters that would break the conversion
• Disable query folding - Add a Table.Buffer() step before the type conversion to force local processing instead of pushing it to Athena
• Alternative workaround - Keep the column as text in Power Query and create a calculated column in the data model: Value([TextColumn])

The root cause is usually Power BI trying to execute the type conversion on Athena's side, which fails due to query translation issues. Processing it locally in Power BI typically resolves this.

Try the custom column approach first - it's usually the most reliable fix for Athena connections.

Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

Best Regards,
Jainesh Poojara | Power BI Developer

[visheshjain]

Hello @jaineshp,

Adding new columns or calculated columns will slow down and the query.

I want the query to fold to work as it will only make the refreshes faster.

Thank you,