Adding a Slicer to a pivot table of a Bigquery Connected Data Source

[chirinek]

Hello!
I have a sheet which is sourced from BQ using Data connector, on which i have applied a pivot table:

When trying to add a slicer to the pivot table and using the connected data as the data range, i get the corrected list of columns, but unfortunately, when i choose a column, it doesn't display the available values on which i can filter on ("Filter on values"), i only get the "Filter by condition" option:

In case i create an extract of the retrieved data, it is limited to 100k rows whereas my data is bigger than that.

Can anyone please advise on a possible solution to that?

Thank you!

[joelvilca]

Hello @chirinek , 

I remember I had the same problem with the row limit that the Bigquery connector has to a spreadsheet.

I would recommend creating your charts in Google's Locker Studio/Datastudio, here is the product documentation that has a very quick connection to Bigquery and a very versatile chart creation guide.

https://cloud.google.com/bigquery/docs/visualize-looker-studio?hl=es-419 

[Snoshone07]

Hi @chirinek it seems the issue you’re encountering is tied to how Google Sheets handles live connections to BigQuery data. Since the data is dynamically queried and not fully loaded into the sheet, certain features, like "Filter on values" in slicers, may not function as expected. Here are a few suggestions to help you work around this limitation:

1. Extract Data into the Sheet

• You can try creating a static table by extracting the necessary data into your Google Sheet, instead of relying on the live connection. This would enable the "Filter on values" feature in your slicer.
• However, as you mentioned, Google Sheets has a limit of 100,000 rows, which might become an issue if your dataset is larger.

2. Filter Data in BigQuery Before Importing

• To reduce the size of the dataset and make it more manageable in Sheets, apply filters directly in your BigQuery query. This will ensure that only the most relevant data is imported, improving performance and usability.

3. Use a BI Tool for More Advanced Features

• If your dataset is too large for Google Sheets or you need more advanced filtering and visualization options, consider switching to a business intelligence tool that connects directly to BigQuery.
• For instance, Looker Studio (formerly Data Studio) integrates seamlessly with Google products, while tools like Windsor.ai also provide robust connections to BigQuery, allowing for advanced filtering and reporting without row limitations.

4. Custom Scripts for Slicers

• As a more technical workaround, you could use Google Apps Script to dynamically populate slicer options by fetching values directly from BigQuery. While this approach requires some coding, it could be an effective solution tailored to your needs.