Menu Close Get started

Learn Metabase

Everything you can do with the table visualization

<em>Fig. 1</em>. The table visualization in action: viewing orders for Fantastic Wool Shirt, then summing up the total revenue for that product.
Fig. 1. The table visualization in action: viewing orders for Fantastic Wool Shirt, then summing up the total revenue for that product.

Tables are data’s natural habitat, with their columns and rows corresponding to relational databases’ fields and records. They may not be as “visual” as a bar chart or a map, but they’re often what you need when you’re working with a lot of fields. The table visualization in Metabase comes packed with features — some automatic, and some you can customize for yourself. In this article, we’ll work with the Orders table in the Sample Dataset included with Metabase to explore the table visualization and lay bare its secrets.

The table visualization

To start, we’ll select Ask a question from the main navigation bar, pick Sample Dataset as our starting dataset, and select the Orders table. And just to be clear on terms here, the Orders table is the set of relations in the database that houses the Sample Dataset. This table is distinct from the table visualization, or just “table”–which is the graphical representation of the data in the table, and the occasion for this article.

Here’s our basic table:

<em>Fig. 2</em>. The <strong>Orders</strong> table displayed as a chart.
Fig. 2. The Orders table displayed as a chart.

Table visualization basics

It might not seem so special at first, but there’s already a lot going on. So before we customize our table, let’s go through the features that come with it out of the box.

Column actions

The options Metabase presents for each column differ depending on the type of data. For example, if you click on the heading of the Total($), Metabase will present a set of options, like Distribution, Sum, Sum over time, Average and so on. If you clicked on the Created At column, you’d get a different set of options, as it wouldn’t make much sense to take the average date, for example. Figure 3 shows us a GIF of selecting the Distribution option for the Created_At column.

<em>Fig. 3</em>. Clicking on the column heading for the <strong>Created At</strong> column and selecting <strong>Distribution</strong> will generate a line chart that plots the count of orders per month over time.
Fig. 3. Clicking on the column heading for the Created At column and selecting Distribution will generate a line chart that plots the count of orders per month over time.

From the line chart, you can continue to drill through the data, like zooming in the orders in a section of the chart, or by clicking on a month to view those orders as a table.

Similarly, if you click on a value in one of the columns, Metabase will present a menu that gives you some options depending on the type of data in that column. For instance, by clicking on a value in the Total column, Metabase will present options to filter the data in relation to that value: greater than, less than, and so on.

<em>Fig. 4</em>. Clicking on a scalar value will present options for filtering the table by that value.
Fig. 4. Clicking on a scalar value will present options for filtering the table by that value.

Detail views

To make records easier to read, you can click on an entity key column, either a primary or foreign key, will bring up a detail view. In figure 5, for example, clicking on the order ID of “3” will bring up details from that order from the Orders table.

<em>Fig. 5</em>. Clicking on an entity key will bring up a detail view for that record.
Fig. 5. Clicking on an entity key will bring up a detail view for that record.

Customizing our table

Now let’s see if we can spruce up our table a bit.

Column settings

You can change settings for each of the columns by either clicking on a column’s heading and selecting the gears icon, or via the Settings sidebar. Metabase will include additional columns from other tables linked via foreign key.

<em>Fig 6</em>. The table visualization's <strong>Settings</strong> sidebar.
Fig 6. The table visualization's Settings sidebar.

Once again, Metabase knows to present different options for different types of data. For example, for scalar values, you can add a mini bar chart to show the value’s position within the range of values in the column.

<em>Fig. 7</em>. Adding a mini bar chart to contextualize the value with respect to the range of values in the column.
Fig. 7. Adding a mini bar chart to contextualize the value with respect to the range of values in the column.

Conditional formatting

You can highlight cells or rows based on the values they contain, which makes it easier for people to see value ranges and outliers. There are two types of conditional formatting: single color and color range. When you want to color a cell or row if a value in cell meets a certain criteria, use the single color option.

<em>Fig. 8</em>. Use the single color option for highlighting cells or rows.
Fig. 8. Use the single color option for highlighting cells or rows.

If instead you want to show the value’s relative position in the range of values for a column (or multiple columns), use the color range option (it’s a subtler version of the mini bar chart).

<em>Fig. 9</em>. Use the color range option to highlight cells based on the relative value.
Fig. 9. Use the color range option to highlight cells based on the relative value.

Adding, removing, and editing columns

You can select which columns you’d like to include in the table. If your table includes foreign keys, Metabase will automatically make available fields from those tables that you could add to your table. Since the Orders table includes foreign keys from the People and Products tables, you can select columns from those tables to add to your table.

<em>Fig. 10</em>. You can add columns from the <strong>Settings</strong> sidebar, or in the <strong>notebook editor</strong>.
Fig. 10. You can add columns from the Settings sidebar, or in the notebook editor.

If you need to add a column from another table (say, the Reviews table), you’ll need to join that table in order to get access to those columns. To learn how, see joins in Metabase.

You can remove a column by clicking on the X next to the column in the column settings. To rearrange columns, simply drag and drop a column, either on the table itself, or in the Settings sidebar.

<em>Fig. 11</em>. To relocate a column, just drag and drop.
Fig. 11. To relocate a column, just drag and drop.

Custom columns

You can create custom columns using the notebook editor. Let’s say you want to include a column that lists the unit price of the product ordered, which we’d calculate by dividing the Subtotal by the Quantity orders. Open up the notebook editor, and select the Custom Column option. Enter the calculation in the Field Formula input box, and then Give it a name.

<em>Fig. 12</em>. Use the notebook editor to create a custom column.
Fig. 12. Use the notebook editor to create a custom column.

To learn more about what you can do with Field Formulas, check out our article on custom expressions in the notebook editor.

Foreign key remapping

Here’s one last cool feature, though it requires an admin to change some settings in the Data Model section. Foreign keys are useful, but they’re generally not meaningful for (human) readers. Instead of displaying a foreign key as a number, say a product ID, it’d be nice to display the values as the product’s Title. Metabase can substitute foreign keys with values from the foreign table that are associated with that entity key. What this means is that instead of showing the Product_ID value, you can set it up so that people will instead see the product Title, like “Lightweight Wool Computer.” Your Metabase admins can set this up in the Admin panel in the Data Model tab. In this case, we’ll select the Orders table, and change the foreign key from the Products table to instead display in the Order table as the Product -> Title.

<em>Fig. 13</em>. Setting Metabase to display the <strong>Product Title</strong> instead of the <strong>Product ID</strong> in the Orders table.
Fig. 13. Setting Metabase to display the Product Title instead of the Product ID in the Orders table.

Adding tables to dashboards gives you even more options

When you add a table to a dashboard, you can add custom destinations to individual columns, wire up filter widgets, and more. To see an example of a table used in a dashboard, check out our article on building a record lookup tool with Metabase.

Further reading