Urgent || Parameter - Connect data with Athena via odbc

[DivyaA]

I have setup a connection to get the data from Athena which is ofcourse via odbc connector and powerbi gateway. Now I need to get the data but I need to set parameters to control the number of records, rangestart and rangeend. 
I used the following m query but I dont think its working as its ending with timeout error as I tried to fetch only 1 days data which shouldn't cross 1L record. Also Im not sure of the parameter so I need your help in fetching the data.

[v-karpurapud]

Hi @DivyaA 

Thank you for submitting your question to the Microsoft Fabric Community Forum, and thanks to @Ahmed-Elfeel for offering helpful suggestions.

Could you let us know if the suggested solution resolved your issue?If you still need help, please share more details so we can assist you further.

Thank you.

[DivyaA]

Hi @v-karpurapud ,
The solution provided did not work for me and I've dropped the requirement as well so thank you for your support.

[v-karpurapud]

Hi @DivyaA 

Thank you for the update. Kindly let us know if this requirement can now be considered closed, or if you would like us to continue exploring a solution for the Athena ODBC parameter setup.
Please feel free to reach out anytime if you wish to revisit this in the future or need support with an alternative approach.

Regards,

Microsoft Fabric Community Support Team.

[DivyaA]

You may close the ticket. Thanking everyone for the support

[DivyaA]

Hi @Ahmed-Elfeel ,
Thank you for the response.
I tried your query but ended with this error. Im not sure of its occurrence as I used correct field name, no shorthand form was used.

[Ahmed-Elfeel]

Hi @DivyaA,

This error message could be:

• Incorrect Table/Column Names (Case sensitivity issues)

• Parameter Data Type Mismatches (Dates not being passed correctly)

• SQL Syntax Issues (Even small syntax errors can cause this)

• ODBC Connection Problems (DSN configuration issues)

You can debug it by:

• First Test Basic Connection First:

let
    Source = Odbc.Query("dsn=your-athena-dsn", "
        SELECT 1 as Test
        LIMIT 1
    ")
in
    Source

•  Then Verify Your Actual Table Structure 
  • What is your actual:

    • Table name in Athena?

    • Date column name exactly as it appears in Athena?

    • Data types of your date columns?

• Then try this Modified Query:

let
    // First, convert parameters to text for debugging
    startDate = Text.From(RangeStart),
    endDate = Text.From(RangeEnd),
    recordLimit = Number.ToText(DevelopmentRecords),
    
    sqlQuery = "
        SELECT * 
        FROM your_actual_table_name 
        WHERE your_actual_date_column >= CAST('" & startDate & "' AS TIMESTAMP)
          AND your_actual_date_column < CAST('" & endDate & "' AS TIMESTAMP)
        LIMIT " & recordLimit,
    
    Source = Odbc.Query("dsn=your-athena-dsn", sqlQuery)
in
    Source

Also here is another approach (With excplicit casting)

let
    Source = Odbc.Query("dsn=your-athena-dsn", "
        SELECT * 
        FROM your_actual_table_name 
        WHERE your_actual_date_column >= CAST(? AS TIMESTAMP)
          AND your_actual_date_column < CAST(? AS TIMESTAMP)
        LIMIT ?
    ", [
        Text.From(RangeStart),
        Text.From(RangeEnd),  
        DevelopmentRecords
    ])
in
    Source

Try the basic connection test first then we can build up from there; The garbled error usually indicates a fundamental syntax or connection issue rather than just parameter problems.  

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[Ahmed-Elfeel]

Hi @DivyaA,

There is :

• Syntax errors in your M code (incomplete statements and missing brackets)

• Parameter data type mismatches (Dates are likely being passed as text)

• Inefficient query structure

Here is Corrected M code :

let
    Source = Odbc.Query("dsn=your-athena-dsn", "
        SELECT * 
        FROM your_table_name 
        WHERE date_column >= ? 
          AND date_column < ? 
        LIMIT ?
    ", [
        RangeStart,    // Parameter 1
        RangeEnd,      // Parameter 2  
        DevelopmentRecords  // Parameter 3
    ])
in
    Source

Note: 

• Make sure to replace dsn=your-athena-dsn with your actual DSN name and your_table_name with your actual Athena table name
• Replace date_column with the actual date/timestamp column name from your Athena table

Make sure your parameters are set up with correct data types:

• Development (Yes): Text type, value "Yes"

• Development Records: Whole Number type, value 500

• Date/Time type, value 5/1/2025 12:00:00 AM (RangeStart)

• Date/Time type, value 5/2/2025 12:00:00 AM (RangeEnd)

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.