ODBC error for data source AWS Athena

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.

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

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!

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.

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.