Tables
Tables are one of the most powerful and versatile data structures in the XlBlocks framework. They allow you to work with structured, tabular data directly within Excel, providing a wide range of functionality for creating, manipulating, and analyzing data. Tables are represented in Excel using object handles, enabling seamless integration with other XlBlocks objects.
Key Features
- Building Tables: Create tables from Excel ranges, delimited files (e.g., CSV), or other data sources.
- Data Manipulation: Perform operations such as filtering, sorting, joining, and grouping.
- Column Operations: Access, modify, or append columns, including support for derived columns using expressions and data from dictionaries or lists.
- Exporting: Save tables to delimited files for external use.
- Integration: Convert tables to dictionaries or lists, and vice versa, for flexible data workflows.
Example Use Cases
- Data Cleaning: Use filtering and null-dropping functions to clean raw data.
- Data Analysis: Perform grouping and aggregation to calculate statistics.
- Data Transformation: Append derived columns or join multiple tables for enriched datasets.
- Data Export: Save processed tables to CSV for sharing or further analysis.
Expression Language
The XlBlocks expression language is a powerful tool for filtering rows and adding new fields to tables. It allows you to write expressions that are evaluated row by row, enabling dynamic and flexible data manipulation. This is expression language is used by the functions XBTable_FilterWith and XBTable_AppendColumnsWith.
For more information, refer to the Expression Language Reference.
Table Functions
XBTable_Build
Build a table from a range
=XBTable_Build(dataRange)
| Parameter | Type | Description |
|---|---|---|
| dataRange | XlBlockRange |
A range of data to use for the table |
Returns
A table
XBTable_BuildWithTypes
Build a table from a range, specifying the column types and names
=XBTable_BuildWithTypes(dataRange, columnTypeRange, columnNameRange)
| Parameter | Type | Description |
|---|---|---|
| dataRange | XlBlockRange |
A range of data to use for the table |
| columnTypeRange | XlBlockRange |
A range of column types |
| columnNameRange | XlBlockRange |
A range of column names |
Returns
A table
XBTable_BuildFromCsv
Build a table from a delimited file
=XBTable_BuildFromCsv(filePath, [delimiter], [hasHeader], [columnNameRange], [columnTypeRange], [encoding])
| Parameter | Type | Description |
|---|---|---|
| filePath | String |
A filepath |
| delimiter | String |
Optional The delimiter to use |
| hasHeader | Boolean |
Optional Flag indicating whether the csv has a header row |
| columnNameRange | XlBlockRange |
Optional A range of column names |
| columnTypeRange | XlBlockRange |
Optional A range of column names |
| encoding | String |
Optional The file encoding |
Returns
A table
XBTable_SaveToCsv
Save a table to a delimited file
=XBTable_SaveToCsv(table, filePath, [delimiter], [includeHeader], [columnNameRange], [columnTypeRange], [encoding], [archivePath])
| Parameter | Type | Description |
|---|---|---|
| table | XlBlockTable |
A table |
| filePath | String |
A filepath |
| delimiter | String |
Optional The delimiter to use |
| includeHeader | Boolean |
Optional Include a header row |
| columnNameRange | XlBlockRange |
Optional A range of column names |
| columnTypeRange | XlBlockRange |
Optional A range of column names |
| encoding | String |
Optional The file encoding |
| archivePath | String |
Optional A relative or absolute path to a directory in which to save a timestamped copy of an existing original file |
Returns
A boolean
XBTable_Get
Get a table
=XBTable_Get(table, [includeHeader])
| Parameter | Type | Description |
|---|---|---|
| table | XlBlockTable |
A table |
| includeHeader | Boolean |
Optional Include header in output |
Returns
A range
XBTable_GetColumn
Get a column in a table
=XBTable_GetColumn(table, columnNameOrNumber, [includeHeader])
| Parameter | Type | Description |
|---|---|---|
| table | XlBlockTable |
A table |
| columnNameOrNumber | Object |
The column name or number |
| includeHeader | Boolean |
Optional Include header in output |
Returns
A range
XBTable_GetColumnAsList
Get a column in a table as a list
=XBTable_GetColumnAsList(table, columnNameOrNumber)
| Parameter | Type | Description |
|---|---|---|
| table | XlBlockTable |
A table |
| columnNameOrNumber | Object |
The column name or number |
Returns
A list
XBTable_ColumnNames
Get the column names of a table
=XBTable_ColumnNames(table)
| Parameter | Type | Description |
|---|---|---|
| table | XlBlockTable |
A table |
Returns
A range
XBTable_ColumnTypes
Get the column types of a table
=XBTable_ColumnTypes(table)
| Parameter | Type | Description |
|---|---|---|
| table | XlBlockTable |
A table |
Returns
A range
XBTable_RowCount
Get the number of rows in a table
=XBTable_RowCount(table)
| Parameter | Type | Description |
|---|---|---|
| table | XlBlockTable |
A table |
Returns
An integer
XBTable_ColumnCount
Get the number of columns in a table
=XBTable_ColumnCount(table)
| Parameter | Type | Description |
|---|---|---|
| table | XlBlockTable |
A table |
Returns
An integer
XBTable_Dim
Get the dimensions of a table as a string
=XBTable_Dim(table)
| Parameter | Type | Description |
|---|---|---|
| table | XlBlockTable |
A table |
Returns
A string
XBTable_LookupValue
Lookup a value in a table
=XBTable_LookupValue(table, lookupColumn, lookupValue, valueColumn, [onMultipleMatches])
| Parameter | Type | Description |
|---|---|---|
| table | XlBlockTable |
A table |
| lookupColumn | String |
The name of the column in which to look up the value |
| lookupValue | Object |
The value to search for |
| valueColumn | String |
The name of the column in the matching row to return a value from |
| onMultipleMatches | String |
Optional Behavior on multiple matching rows, one of 'error', 'first' or 'last' |
Returns
An object
XBTable_Join
Join two tables
=XBTable_Join(leftTable, rightTable, joinType, [joinOn], [leftSuffix], [rightSuffix], [includeDuplicateJoinColumns])
| Parameter | Type | Description |
|---|---|---|
| leftTable | XlBlockTable |
The left table to join |
| rightTable | XlBlockTable |
The right table to join |
| joinType | String |
The join type, one of 'full', 'inner', 'right' or 'left' |
| joinOn | XlBlockRange |
Optional The keys to join on, defaults to all common columns |
| leftSuffix | String |
Optional The suffix to apply to shared non-key columns from the left table |
| rightSuffix | String |
Optional The suffix to apply to shared non-key columns from the right table |
| includeDuplicateJoinColumns | Boolean |
Optional Include both sets of identical joined columns in output |
Returns
A table
XBTable_UnionAll
Combine rows of multiple matching tables
=XBTable_UnionAll(table)
| Parameter | Type | Description |
|---|---|---|
| table | Any |
A table |
Returns
A table
XBTable_Union
Combine rows of multiple matching tables, removing duplicates
=XBTable_Union(table)
| Parameter | Type | Description |
|---|---|---|
| table | Any |
A table |
Returns
A table
XBTable_UnionSuperset
Combine multiple tables, returning a table with the superset of all columns
=XBTable_UnionSuperset(table)
| Parameter | Type | Description |
|---|---|---|
| table | Any |
A table |
Returns
A table
XBTable_DropNulls
Drop rows containing nulls from a table
=XBTable_DropNulls(table, dropNullBehavior)
| Parameter | Type | Description |
|---|---|---|
| table | XlBlockTable |
A table |
| dropNullBehavior | String |
Drop behavior, 'any' to drop rows containing any nulls, 'all' to drop rows containing only nulls |
Returns
A table
XBTable_Sort
Sort a table by one or more columns
=XBTable_Sort(table, sortColumns, [descending], [nullsFirst])
| Parameter | Type | Description |
|---|---|---|
| table | XlBlockTable |
A table |
| sortColumns | XlBlockRange |
Columns to sort on |
| descending | XlBlockRange |
Optional Sort in descending order |
| nullsFirst | XlBlockRange |
Optional Sort null values first |
Returns
A table
XBTable_Filter
Filter a table by values in a column
=XBTable_Filter(table, filterColumn, filterValue, [inclusive])
| Parameter | Type | Description |
|---|---|---|
| table | XlBlockTable |
A table |
| filterColumn | String |
Column to filter on |
| filterValue | Object |
The value to filter on |
| inclusive | Boolean |
Optional Use inclusive filtering |
Returns
A table
XBTable_FilterWith
Filter a table with an expression
=XBTable_FilterWith(table, filterExpression)
| Parameter | Type | Description |
|---|---|---|
| table | XlBlockTable |
A table |
| filterExpression | String |
A filter expression |
Returns
A table
XBTable_AppendColumnsWith
Filter a table with an expression
=XBTable_AppendColumnsWith(table, columnNames, columnExpressions)
| Parameter | Type | Description |
|---|---|---|
| table | XlBlockTable |
A table |
| columnNames | XlBlockRange |
Column names |
| columnExpressions | XlBlockRange |
Column expressions |
Returns
A table
XBTable_AppendColumnFromList
Append a column to a table from a dictionary
=XBTable_AppendColumnFromList(table, list, columnName, [columnType])
| Parameter | Type | Description |
|---|---|---|
| table | XlBlockTable |
A table |
| list | XlBlockList |
A list |
| columnName | String |
The name to use for the new column |
| columnType | String |
Optional The type to use for the new column column |
Returns
A table
XBTable_AppendColumnFromDict
Append a column to a table from a dictionary
=XBTable_AppendColumnFromDict(table, dictionary, keyColumnName, valueColumnName, [valueColumnType], [valueOnMissing])
| Parameter | Type | Description |
|---|---|---|
| table | XlBlockTable |
A table |
| dictionary | XlBlockDictionary |
A dictionary |
| keyColumnName | String |
The name of the column used to match the dictionary keys |
| valueColumnName | String |
The name to use for the value column |
| valueColumnType | String |
Optional The type to use for the value column |
| valueOnMissing | Object |
Optional The value to use for keys not found in dictionary |
Returns
A table
XBTable_ToDict
Create a dictionary from two table columns
=XBTable_ToDict(table, keyColumnName, valueColumnName, [onDuplicateKeys])
| Parameter | Type | Description |
|---|---|---|
| table | XlBlockTable |
A table |
| keyColumnName | String |
The name of the column to use for keys |
| valueColumnName | String |
The name of the column to use for values |
| onDuplicateKeys | String |
Optional Behavior on duplicate keys, one of 'error', 'first' or 'last' |
Returns
A dictionary
XBTable_ToDictofDicts
Append a column to a table from a dictionary
=XBTable_ToDictofDicts(table, keyColumnName, [onDuplicateKeys])
| Parameter | Type | Description |
|---|---|---|
| table | XlBlockTable |
A table |
| keyColumnName | String |
The name of the key column |
| onDuplicateKeys | String |
Optional Behavior on duplicate keys, one of 'error', 'first' or 'last' |
Returns
A dictionary
XBTable_Projection
Project a table onto a new table, optionally changing column names and types
=XBTable_Projection(table, currentColumnNames, [newColumnNames], [newColumnTypes])
| Parameter | Type | Description |
|---|---|---|
| table | XlBlockTable |
A table |
| currentColumnNames | XlBlockRange |
Names of columns to include in the projection |
| newColumnNames | XlBlockRange |
Optional Names to use to rename the columns in the projection |
| newColumnTypes | XlBlockRange |
Optional Types to use to convert the columns in the projection |
Returns
A table
XBTable_GroupBy
Perform aggregation operations on a table
=XBTable_GroupBy(table, groupByColumns, [groupByOperation], [aggregateColumns], [newColumnNames])
| Parameter | Type | Description |
|---|---|---|
| table | XlBlockTable |
A table |
| groupByColumns | XlBlockRange |
Names of columns to group by |
| groupByOperation | XlBlockRange |
Optional Group by operation(s), one or more of 'sum', 'product', 'min' 'max' 'mean', 'median', 'count', 'counta', 'first', 'firsta', 'last', 'lasta', 'stddev', 'stddevp', 'var', 'varp', 'skew', 'skewp', 'kurt' or 'kurp' |
| aggregateColumns | XlBlockRange |
Optional Names of columns to aggregate, defaults to all numeric non-group columns |
| newColumnNames | XlBlockRange |
Optional Names to use for the aggregate output columns, defaults to '[ColumnName].[Operation]' |
Returns
A table