Connecting Big Query view with partitioning

[lg_analyst]

Hi, I'm trying to connect to a BigQuery view which is linked to a table that is partitioned on orderDate and requires a filter on orderDate for the query to run. I'm connecting via the Power BI dataflow. When connecting to the actual table (orders) I am able to add a filter on orderDate and connect correctly. However, when attempting to connect to the view (orders_test) it returns an ODBC error. 

Advanced editor: 

let
  Source = GoogleBigQuery.Database([BillingProject = null, UseStorageApi = null, ConnectionTimeout = null, CommandTimeout = null]),
  Navigation = Source{[Name = "bi-stg"]}[Data],
  #"Navigation 1" = Navigation{[Name = "bi", Kind = "Schema"]}[Data],
  #"Navigation 2" = #"Navigation 1"{[Name = "orders_test", Kind = "View"]}[Data],
  #"Filtered rows" = Table.SelectRows(#"Navigation 2", each [i_orderDate] = #date(2022, 1, 4))
in
  #"Filtered rows"

Error:

DataSource.Error: ODBC: ERROR [42000] [Microsoft][BigQuery] (70) Invalid query: Cannot query over table 'bi-stg.bi.orders' without a filter over column(s) 'orderDate' that can be used for partition eliminationDetailsDataSourceKind = GoogleBigQuery
DataSourcePath = GoogleBigQuery
OdbcErrors = #table({"SQLState", "NativeError", "Message"}, {})

Note that the error message is referring to the original table (orders) and it's orderDate column, not to the view (orders_test) and it's orderDate column (i_orderDate) which I'm trying to query. I tried changing the Filtered Rows step and specifying orderDate instead of i_orderDate but receive the same error. It's assuming I need to add a filter to the original table even though I'm trying to query the view. Can anyone help? Thanks!

[lg_analyst]

Hi Jing, thank you for your answer. I can't disable the require partition filter because the table is also used by other users (other than Power BI) and the filter is necessary to try and contain costs as much as possible. 

Using Power Bi Desktop I have the same issue 

[v-jingzhang]

Hi @lg_analyst 

See if these links help:

https://cloud.google.com/bigquery/docs/querying-partitioned-tables#require_a_partition_filter_in_que... 

https://www.yuichiotsuka.com/bigquery-table-partition/ 

https://tipsfordev.com/how-can-i-import-a-partitioned-table-from-bigquery-that-requires-a-partition-... 

I'm not sure how the SQL query is generated when using a view. It seems the actual table has been set to require Partition Filters in received queries. However, the query currently sent to the actual table doesn't include the valid date filter clause correctly so it indicates this error message. See if you can uncheck the option Require partition filter for the actual table in BigQuery?

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[lg_analyst]

Hi @v-jingzhang thank you but I'm looking to query a view, not a table (querying a table works) and removing the partition filter kind of defeats the point

[v-jingzhang]

Hi @lg_analyst 

A view is a virtual table defined by a SQL query. When you create a view, you query it in the same way you query a table. When a user queries the view, the query results contain data only from the tables and fields specified in the query that defines the view. So I think disable the option Require partition filter for the actual table might be helpful.

In addition, have you performed the same operations in Power Query Desktop? If it works in Power Query Desktop, you can try copying the query from Desktop to Dataflow and check the result. 

Jing