Data Tables

Data Tables sit on top of your spreadsheet, are anchored from the upper left most cell the data table covers, and include all the data from the imported data set. They are a convenient way to view and manage large data sets within a spreadsheet for a number of reasons:

1. Live Connections - Data tables support live connections to hosted data and can be refreshed to pull in new data. These are called Connected Tables. Pivot tables and graphs can be created from data tables. When Pivot tables and graphs are created from connected tables, they will update every time the connected table is refreshed with the latest data.
2. Size - Data table size will not exceed the existing border even when the data set is updated with new data. Data inside the data table can be viewed by scrolling or ctrl arrowing within the boundary. To view more or less off the data inside the table, click and drag the bottom border to increase or decrease the number of rows shown. Write formulas below or adjacent to the data table and they will update when the data updates but won't be overwritten by an expanding data set.
3. Read-only - Data tables are read only so data contained within cannot be intentionally or unintentionally overwritten.
4. Transformation - Data tables do support column level transformations by simply typing a formula in the spreadsheet and referencing a specific row in the data table rather than having to fill a function down for millions of rows. If working in a traditional spreadsheet mode is preferred, see the Explode feature below.

Connected Tables

Connect tables are data tables backed by a data source connection. Row Zero supports a number of data warehouse connectors that allow users to query their data warehouse and import data directly into a Row Zero spreadsheet. Once imported to the workbook, the data sits in a connected table, which simply data tables backed by a data source connection. Any time the query backing the connected table is run, the data in the connected table is updated with the latest query results.

Auto-refresh

Connected tables backed by a data source can be scheduled to refresh at a specific time each day. The auto-refresh control is found in the upper right corner of the data connections window and is only available to business users. When the auto-refresh occurs, the query backing the data import will be re-executed and any new results from the query will populate in the table. Formulas, computed columns, pivot tables, and graphs built off of the connected table will also update. When viewing a connected table, you can see when it was last updated by overing over the header of the table.

Data Table Transformations

While Data Tables are preferable for working with large datasets and performing column level transformations, there are instances where it is helpful to be able to place an entire dataset in a spreadsheet or perform cell level computations and transformations are needed. Conversely, you may want to transform data in a spreadsheet and then transform it back to a data table. The two functions below outline those capabilities.

Explode - Data table to spreadsheet

To transform a data table into spreadsheet format, right-click on the data table of interest and select 'Explode table' from the context menu. Once selected the data table will be pasted into the underlying cells of the spreadsheet filling all the rows populated by the data. This is similar to a copy/paste values.

Spreadsheet to data table

To convert a data set currently sitting in the cells of a spreadsheet to a data table use the DATATABLE() function.

Example data table creation in a spreadsheet:

=DATATABLE(A1:B9)

Import to specific cell

Data tables originate from the upper left most corner of the table. When importing a data table, the import window allows a user to select 'import to current sheet' and a specific cell.

Click and drag

Data tables can be moved within a sheet by clicking on the left, right, and top borders of the table and dragging to a new location. The hand icon will display when the mouse is hovering over a border that can be moved.

Resize

Data tables default to 15 rows and can be expanded or contracted by dragging the bottom border of the data table to the desired size.

Add New Columns or Computed Columns

Columns can be added to data tables by typing the function or transformation in the column adjacent to the data table. The function or transformation will be executed on any column selected and will produce a new column in the data table.

1. Write a function
2. Evaluate the function and the column is added to the data table.
3. Rename a computed column by right-clicking on the column and selecting "Rename column"

Delete/hide Column

Data table columns cannot be permanently deleted but they can easily be hidden by right clicking on the data table and selecting 'manage columns' and de-selecting the columns to be hidden.

Scroll

Mouse over a data table to scroll through the data. Position is indicated by a scroll bar that appears when the mouse is positioned over the data table. Ctrl+arrow up or down will take you to the top or bottom of the data table.

Data Table Interactions

Rename Column

Columns in a data table can be renamed by selecting the header cell and typing in a new name.

Edit Data

The data inside a data table cannot be edited. It can be manipulated on a column level by creating new columns or using filter and sort features.

Format Data

Data in data table columns can be formatted by selecting the column of interest and using standard spreadsheet formatting buttons or keyboard shortcuts.

Calculations

Calculations can be performed on data tables columns. For example, functions like SUM, MEDIAN, COUNTIF, and others can be run on individual columns within a data table. To execute a function, type your function in the desired cell and select the column of interest in the data table by clicking on it or arrowing over to select it. You will see the name of the column auto-fill in your function.

Filter and Sort

Filter/Sort Instructions can be found at the link here.