Pruning BigQuery partitions with Data studio

I've got an almost identical scenario to this question:

How to choose the latest partition in BigQuery table?

With one additional complication. I need the result to display in Data Studio.

The setup

I've got a series of data sets which appear at different time intervals apart and I need to get the most recent partition. Because they're inconsistent time periods apart I can't just get the last day and use that.

I can use BigQuery scripting to successfully prune the queries with a dynamic query, but when I move this query into Data Studio the query doesn't load properly.

The table loads in the data sources part:

But when I actually try to use it in the report:

Data Studio cannot connect to your data set.

Failed to fetch data from the underlying data set

Error ID: e6546a97

Is there a way to get Data Studio to display this properly with pruning?

Example query

DECLARE max_date DATE;
SET max_date = (SELECT DATE(MAX(_partitiontime)) FROM `dataset.table`);   

SELECT *
FROM `dataset.table`
WHERE DATE(_partitiontime ) = max_date

from Recent Questions - Stack Overflow https://ift.tt/3yCU2ul
https://ift.tt/3bVaGvz