Direct Query Failing to Filter Strings With Apostrophes (ODBC: ERROR [42000])

Hi malishan 

You're encountering a known issue when using DirectQuery mode in Power BI with Google BigQuery, specifically when string values contain apostrophes (single quotes)—for example, names like "Larry's Diner". While the data loads fine initially, applying a visual-level filter on a value that includes an apostrophe triggers a syntax error from BigQuery, indicating a malformed query (e.g., Expected ")" but got string literal...). This happens because Power BI’s query folding engine escapes string literals improperly in some scenarios when generating SQL for DirectQuery. In this case, it's failing to properly escape or wrap values containing single quotes, which leads to broken SQL syntax once sent to BigQuery.

Although this behavior is not unique to your setup, it is a long-standing limitation in how Power BI formats DirectQuery statements to BigQuery and other ODBC-based sources. When working in Import mode, Power BI handles these characters more gracefully since it processes them locally, but in DirectQuery mode, the issue occurs at the query translation layer.

As a workaround—without stripping out apostrophes from your actual data—you can try:

Creating a calculated column or Power Query transformation that replaces single quotes with a safe placeholder for display or filtering purposes, while keeping the original value for other purposes.

Alternatively, switch the report to Import mode, which avoids query folding to BigQuery entirely and thus circumvents this issue.

Some users have also had success by wrapping the problematic field in a custom SQL query or parameterized query, which forces Power BI to treat the value as a literal string properly escaped.