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

[Anonymous]

I'm connected to a BigQuery database and am using Direct Query to access the data. When I load in a field of strings with apostrophes in some of the values (eg. "Larry's Diner"), everything will initially load fine into a visual, but when you use a visual level filter to select on of  the apostrophed names, this error occurs:
Release:
October 2018

Product Version:
2.63.3272.40461 (18.10) (x64)

Error Message:
OLE DB or ODBC error: [DataSource.Error] ODBC: ERROR [42000] [Microsoft][BigQuery] (70) Invalid query: Syntax error: Expected ")" but got string literal 'S BAKERY' at [63:42].

It certainly seems like this is a Power BI problem, since BigQuery runs the same queries no problem on its own. So my questions are: is this a known Power BI issue and is there an available workaround that doesn't involve removing the apostrophes from the values?

[Poojara_D12]

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.

[malishan]

Hi @Anonymous 

Has there been any update on this? It's been over 7 years.

Thanks

[4itk]

Is there a solution since 2018?

The latest version in 2025 is still failing with an apostrophe in SQL to ODBC

[gjohnson]

Hi @Anonymous ,

We have also come up against this issue. Having a timeframe for a fix would help us to determine the proper work around.

Thanks,

Georgette

[Anonymous]

@Anonymous,

The engineer is working on a fix for this issue.

Regards,
Lydia

[Anonymous]

Hi @Anonymous ,

We found this issue affecting our company as well. What's the general timeframe to have a fix like this rolled out?

Thanks,

Nick

[Anonymous]

Awesome! Thanks for all your help.

[Anonymous]

@Anonymous,

I can reproduce a similar error as yours, I have reported this issue internally:CRI 90186123, and will post back once I get any updates.

Regards,
Lydia