Documentation

Updates to Queries, Filters, Sorts & Joins - May 2024

Below is a summary of everything that's new and improved with the query experience in Equals.

But first thing's first, here's how the migration to the new query experience will work:

  • Workspaces will be migrated in phases, starting the week of May 13 2024
  • A modal will appear in the Equals app when the new query experience is available for your workspace.
  • At that point, your workspace will be able to opt-in from your workspace Settings page.

Questions? Don't hesitate to contact us at [email protected] or via the messenger icon in the bottom-right of your screen.

What's changing?

Connecting a sheet to a datasource

When you create a new workbook, you'll be prompted to start with a blank sheet or a connected sheet. Select connected sheet in order to select a datasource and begin building or writing your query.

When creating a new sheet in an existing workbook, click Connect data in the top-right of the toolbar to connect that sheet to a data source.

Once you have selected a datasource to connect to, you'll be prompted to choose between the Query builder and SQL editor. You can switch between query editors at any point from the Settings panel, accessible from the right side of the toolbar.

Opening the query builder and SQL editor

If you selected Query builder after connecting a sheet to a datasource, you'll see a Query builder toggle in the left side of your toolbar alongside the AI Assist icon on the connected sheet. Use this toggle to open and close the Query builder. The same applies if you had selected SQL editor.

Preview mode in the query builder

The new query builder provides an instant preview of the first 100 rows of your query results as you build, making finding and selecting the exact data you need faster and more fluid.

If you've already run a query and want to make changes, those changes will be instantly previewed on the first 100 rows of your modified query results. Existing data from previous query runs in the connected sheet will not be changed unless you choose to run the modified query.

📘

Because BigQuery connections are charged for each column queried, if you have an active BigQuery datasource, you will need to opt-in to the new Query Builder. To do so, go to your BigQuery settings from the Datasources page in Equals.

Filters

We've simplified how filters work by combining query and sheet filters into a single system. Sheet filters are only visible to the user who configured them. They hide rows rather than remove underlying data from the connected sheet. Query filters do remove filtered data from the connected sheet.

Applying filters in the query builder

When building a query, select the + button next to Filters. Available values to filter on will be pre-populated in a drop-down menu so that you can easily identify and select values to filter on. You'll also be able to define custom conditional filtering rules.

Conditional filter rules include:

  • Equal to and Not equal to
  • Starts with and Ends with
  • Contains and Does not contain
  • Greater than and Greater than or equal
  • Less than and Less than or equal
  • n days ago, Greater than n days ago and Less than n days ago

📘

When your Workspace migrates to the new query experience, only filters that are compatible with the above conditional filtering rules will be maintained.

Applying sheet filters to saved query results

In the query builder

When you apply a filter to a column in the connected sheet, you'll have the option to add the filter to the query or clear it.

In the SQL editor

You can apply sheet filters, but you cannot add applied sheet filters to the query.

Sorts

Today, you can sort queried data in a sheet by applying either a query sort or a sheet sort (via the column headers). Sheet sorts are deprecated going forward.

Sorts on connected sheets

Any existing sheet sorts on a connected sheet will be removed and added to your query before it's re-run. Any sorts you add via column headers in a connected sheet will automatically be added to the query.

Sorts on regular sheets

Sorts will continue to work as they always have on regular sheets, i.e. those not connected to a datasource. Sorts that are applied to your Worksheet will be removed on the next query run.

📘

Sheet filters and sorts are private to each user.

Calculated Columns and Data Notes

We've introduced a new Personal setting to allow you to choose if you want an empty calculated column to be created next to data returned when you run a query on a connected sheet. This setting is on by default.

When toggled on, a calculated column is automatically added next to your queried data after the query is run.

When toggled off, a calculated column is not added by default, but you can always manually add one as well as notes columns.

To add a calculated column or note column from the query builder, select + Add column.

You can also add calculated columns by right-clicking a column header in your connected sheet and choosing Add calculated column. Once added, calculated columns can be reordered directly from the query builder by dragging and dropping.

Joins

We've introduced a new, more visual Joins experience from the query builder. To add a join, select the + Join column button. This will open a dedicated modal that allows you to add one or multiple columns from any other table or datasource.

📘

The query builder only supports LEFT joins. The SQL editor supports all join types.