Table functions

  • Article

These functions create and manipulate table values.

Table construction

Name Description
ItemExpression.From Returns the abstract syntax tree (AST) for the body of a function.
ItemExpression.Item An abstract syntax tree (AST) node representing the item in an item expression.
RowExpression.Column Returns an abstract syntax tree (AST) that represents access to a column within a row expression.
RowExpression.From Returns the abstract syntax tree (AST) for the body of a function.
RowExpression.Row An abstract syntax tree (AST) node representing the row in a row expression.
Table.FromColumns Returns a table from a list containing nested lists with the column names and values.
Table.FromList Converts a list into a table by applying the specified splitting function to each item in the list.
Table.FromRecords Returns a table from a list of records.
Table.FromRows Creates a table from the list where each element of the list is a list that contains the column values for a single row.
Table.FromValue Returns a table with a column containing the provided value or list of values.
Table.FuzzyGroup Groups the rows of a table by fuzzily matching values in the specified column for each row.
Table.FuzzyJoin Joins the rows from the two tables that fuzzy match based on the given keys.
Table.FuzzyNestedJoin Performs a fuzzy join between tables on supplied columns and produces the join result in a new column.
Table.Split Splits the specified table into a list of tables using the specified page size.
Table.WithErrorContext This function is intended for internal use only.
Table.View Creates or extends a table with user-defined handlers for query and action operations.
Table.ViewError Creates a modified error record which won't trigger a fallback when thrown by a handler defined on a view (via Table.View).
Table.ViewFunction Creates a function that can be intercepted by a handler defined on a view (via Table.View).

Conversions

Name Description
Table.ToColumns Returns a list of nested lists each representing a column of values in the input table.
Table.ToList Returns a table into a list by applying the specified combining function to each row of values in a table.
Table.ToRecords Returns a list of records from an input table.
Table.ToRows Returns a nested list of row values from an input table.

Information

Name Description
Table.ApproximateRowCount Returns the approximate number of rows in the table.
Table.ColumnCount Returns the number of columns in a table.
Table.IsEmpty Returns true if the table does not contain any rows.
Table.Profile Returns a profile of the columns of a table.
Table.RowCount Returns the number of rows in a table.
Table.Schema Returns a table containing a description of the columns (i.e. the schema) of the specified table.
Tables.GetRelationships Returns the relationships among a set of tables.

Row operations

Name Description
Table.AlternateRows Returns a table containing an alternating pattern of the rows from a table.
Table.Combine Returns a table that is the result of merging a list of tables. The tables must all have the same row type structure.
Table.FindText Returns a table containing only the rows that have the specified text within one of their cells or any part thereof.
Table.First Returns the first row from a table.
Table.FirstN Returns the first row(s) of a table, depending on the countOrCondition parameter.
Table.FirstValue Returns the first column of the first row of the table or a specified default value.
Table.FromPartitions Returns a table that is the result of combining a set of partitioned tables into new columns. The type of the column can optionally be specified, the default is any.
Table.InsertRows Returns a table with the list of rows inserted into the table at an index. Each row to insert must match the row type of the table..
Table.Last Returns the last row of a table.
Table.LastN Returns the last row(s) from a table, depending on the countOrCondition parameter.
Table.MatchesAllRows Returns true if all of the rows in a table meet a condition.
Table.MatchesAnyRows Returns true if any of the rows in a table meet a condition.
Table.Partition Partitions the table into a list of groups number of tables, based on the value of the column of each row and a hash function. The hash function is applied to the value of the column of a row to obtain a hash value for the row. The hash value modulo groups determines in which of the returned tables the row will be placed.
Table.PartitionValues Returns information about how a table is partitioned.
Table.Range Returns the specified number of rows from a table starting at an offset.
Table.RemoveFirstN Returns a table with the specified number of rows removed from the table starting at the first row. The number of rows removed depends on the optional countOrCondition parameter.
Table.RemoveLastN Returns a table with the specified number of rows removed from the table starting at the last row. The number of rows removed depends on the optional countOrCondition parameter.
Table.RemoveRows Returns a table with the specified number of rows removed from the table starting at an offset.
Table.RemoveRowsWithErrors Returns a table with all rows removed from the table that contain an error in at least one of the cells in a row.
Table.Repeat Returns a table containing the rows of the table repeated the count number of times.
Table.ReplaceRows Returns a table where the rows beginning at an offset and continuing for count are replaced with the provided rows.
Table.ReverseRows Returns a table with the rows in reverse order.
Table.SelectRows Returns a table containing only the rows that match a condition.
Table.SelectRowsWithErrors Returns a table with only the rows from table that contain an error in at least one of the cells in a row.
Table.SingleRow Returns a single row from a table.
Table.Skip Returns a table that does not contain the first row or rows of the table.
Table.SplitAt Returns a list containing the first count rows specified and the remaining rows.

Column operations

Name Description
Table.Column Returns the values from a column in a table.
Table.ColumnNames Returns the names of columns from a table.
Table.ColumnsOfType Returns a list with the names of the columns that match the specified types.
Table.DemoteHeaders Demotes the header row down into the first row of a table.
Table.DuplicateColumn Duplicates a column with the specified name. Values and type are copied from the source column.
Table.HasColumns Indicates whether the table contains the specified column(s).
Table.Pivot Given a table and attribute column containing pivotValues, creates new columns for each of the pivot values and assigns them values from the valueColumn. An optional aggregationFunction can be provided to handle multiple occurrence of the same key value in the attribute column.
Table.PrefixColumns Returns a table where the columns have all been prefixed with a text value.
Table.PromoteHeaders Promotes the first row of the table into its header or column names.
Table.RemoveColumns Returns a table without a specific column or columns.
Table.ReorderColumns Returns a table with specific columns in an order relative to one another.
Table.RenameColumns Returns a table with the columns renamed as specified.
Table.SelectColumns Returns a table that contains only specific columns.
Table.TransformColumnNames Transforms column names by using the given function.
Table.Unpivot Given a list of table columns, transforms those columns into attribute-value pairs.
Table.UnpivotOtherColumns Translates all columns other than a specified set into attribute-value pairs, combined with the rest of the values in each row.

Transformation

Name Description
Table.AddColumn Adds a column named newColumnName to a table.
Table.AddFuzzyClusterColumn Adds a new column with representative values obtained by fuzzy grouping values of the specified column in the table.
Table.AddIndexColumn Returns a table with a new column with a specific name that, for each row, contains an index of the row in the table.
Table.AddJoinColumn Performs a nested join between table1 and table2 from specific columns and produces the join result as a newColumnName column for each row of table1.
Table.AddKey Adds a key to table.
Table.AggregateTableColumn Aggregates tables nested in a specific column into multiple columns containing aggregate values for those tables.
Table.CombineColumns Table.CombineColumns merges columns using a combiner function to produce a new column. Table.CombineColumns is the inverse of Table.SplitColumns.
Table.CombineColumnsToRecord Combines the specified columns into a new record-valued column where each record has field names and values corresponding to the column names and values of the columns that were combined.
Table.ConformToPageReader This function is intended for internal use only.
Table.ExpandListColumn Given a column of lists in a table, create a copy of a row for each value in its list.
Table.ExpandRecordColumn Expands a column of records into columns with each of the values.
Table.ExpandTableColumn Expands a column of records or a column of tables into multiple columns in the containing table.
Table.FillDown Replaces null values in the specified column or columns of the table with the most recent non-null value in the column.
Table.FillUp Returns a table from the table specified where the value of the next cell is propagated to the null values cells above in the column specified.
Table.FilterWithDataTable This function is intended for internal use only.
Table.Group Groups table rows by the values of key columns for each row.
Table.Join Joins the rows of table1 with the rows of table2 based on the equality of the values of the key columns selected by table1, key1 and table2, key2.
Table.Keys Returns the keys of the specified table.
Table.NestedJoin Joins the rows of the tables based on the equality of the keys. The results are entered into a new column.
Table.ReplaceErrorValues Replaces the error values in the specified columns with the corresponding specified value.
Table.ReplaceKeys Replaces the keys of the specified table.
Table.ReplaceRelationshipIdentity This function is intended for internal use only.
Table.ReplaceValue Replaces oldValue with newValue in specific columns of a table, using the provided replacer function, such as text.Replace or Value.Replace.
Table.SplitColumn Returns a new set of columns from a single column applying a splitter function to each value.
Table.TransformColumns Transforms the values of one or more columns.
Table.TransformColumnTypes Transforms the column types from a table using a type.
Table.TransformRows Transforms the rows from a table using a transform function.
Table.Transpose Returns a table with columns converted to rows and rows converted to columns from the input table.

Membership

Name Description
Table.Contains Determines whether the a record appears as a row in the table.
Table.ContainsAll Determines whether all of the specified records appear as rows in the table.
Table.ContainsAny Determines whether any of the specified records appear as rows in the table.
Table.Distinct Removes duplicate rows from a table, ensuring that all remaining rows are distinct.
Table.IsDistinct Determines whether a table contains only distinct rows.
Table.PositionOf Determines the position or positions of a row within a table.
Table.PositionOfAny Determines the position or positions of any of the specified rows within the table.
Table.RemoveMatchingRows Removes all occurrences of rows from a table.
Table.ReplaceMatchingRows Replaces specific rows from a table with the new rows.

Ordering

Name Description
Table.Max Returns the largest row or rows from a table using a comparisonCriteria.
Table.MaxN Returns the largest N rows from a table. After the rows are sorted, the countOrCondition parameter must be specified to further filter the result.
Table.Min Returns the smallest row or rows from a table using a comparisonCriteria.
Table.MinN Returns the smallest N rows in the given table. After the rows are sorted, the countOrCondition parameter must be specified to further filter the result.
Table.AddRankColumn Appends a column with the ranking of one or more other columns.
Table.Sort Sorts the rows in a table using a comparisonCriteria or a default ordering if one is not specified.

Other

Name Description
Table.Buffer Buffers a table into memory, isolating it from external changes during evaluation.

Parameter Values

Naming output columns

This parameter is a list of text values specifying the column names of the resulting table. This parameter is generally used in the Table construction functions, such as Table.FromRows and Table.FromList.

Comparison criteria

Comparison criterion can be provided as either of the following values:

  • A number value to specify a sort order. More information: Sort order

  • To compute a key to be used for sorting, a function of one argument can be used.

  • To both select a key and control order, comparison criterion can be a list containing the key and order.

  • To completely control the comparison, a function of two arguments can be used that returns -1, 0, or 1 given the relationship between the left and right inputs. Value.Compare can be used to delegate this logic.

For examples, go to the description of Table.Sort.

Count or Condition criteria

This criteria is generally used in ordering or row operations. It determines the number of rows returned in the table and can take two forms, a number or a condition.

  • A number indicates how many values to return inline with the appropriate function.

  • If a condition is specified, the rows containing values that initially meet the condition is returned. Once a value fails the condition, no further values are considered.

More information: Table.FirstN, Table.MaxN

Handling of extra values

Extra values are used to indicate how the function should handle extra values in a row. This parameter is specified as a number, which maps to the following options:

ExtraValues.List = 0 ExtraValues.Error = 1 ExtraValues.Ignore = 2

More information: Table.FromList, ExtraValues.Type

Missing column handling

This parameter is used to indicate how the function should handle missing columns. This parameter is specified as a number, which maps to the following options:

MissingField.Error = 0 MissingField.Ignore = 1 MissingField.UseNull = 2;

This parameter is used in column or transformation operations, for examples, in Table.TransformColumns. More information: MissingField.Type

Sort Order

Sort ordering is used to indicate how the results should be sorted. This parameter is specified as a number, which maps to the following options:

Order.Ascending = 0 Order.Descending = 1

More information: Order.Type

Equation criteria

Equation criteria for tables can be specified as either:

  • A function value that is either:

    • A key selector that determines the column in the table to apply the equality criteria.

    • A comparer function that is used to specify the kind of comparison to apply. Built-in comparer functions can be specified. More information: Comparer functions

  • A list of the columns in the table to apply the equality criteria.

For examples, go to the description of Table.Distinct.