BigQuery FORMAT_DATE and FORMAT_DATETIME Not Working in Power BI

[jdwalker5]

Hello,

I'm trying to load a dataset from a BigQuery table and it fails if I use the FORMAT_DATE or FORMAT_DATETIME function in my query.

Query:

SELECT

  FORMAT_DATETIME('%Y/%d/%m', datetime_column)

FROM BQ_Table

Error:

I confirmed the data type in the column is datetime.  I know that the issue is related to those functions because if I remove them the query works just fine.  I'm not sure if the functions themselves are not supported or if its related to the % character that is used within them...or something else entirely.

[amitchandak]

@jdwalker5 , on the step, you have the option to view the source query on right click. Open that and check if the right query is getting generated .

Also make sure you have written the query in the Advance option connection, and have not changed it manually on the advance editor of the power query, because that will not handle the special characters

[jdwalker5]

Appreciate the quick response, @amitchandak 

In this example I am building a blank report from scratch where this is the only connection, and I am indeed pasting my query into the advanced options window in the connection setup.

I think the issue is the special characters because I'm getting the same 'Invalid query' error when I use PARSE_DATE or PARSE_DATETIME.