Share via


Table functions

These functions create and manipulate table values.

Table construction

Name Description
#table Creates a table value from columns and rows.
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 Creates a table from a list of columns and specified values.
Table.FromList Converts a list into a table by applying the specified splitting function to each item in the list.
Table.FromRecords Converts a list of records into a table.
Table.FromRows Creates a table from a list of row values and optional columns.
Table.FromValue Creates a table with a column from the provided value or values.
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 that 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 Creates a list of nested lists of column values from a table.
Table.ToList Converts a table into a list by applying the specified combining function to each row of values in the table.
Table.ToRecords Converts a table to a list of records.
Table.ToRows Creates a list of nested lists of row values from a table.

Information

Name Description
Table.ApproximateRowCount Returns the approximate number of rows in the table.
Table.ColumnCount Returns the number of columns in the table.
Table.IsEmpty Indicates whether the table contains any rows.
Table.PartitionValues Returns information about how a table is partitioned.
Table.Profile Returns a profile of the columns of a table.
Table.RowCount Returns the number of rows in the table.
Table.Schema Returns a table containing a description of the columns (that is, the schema) of the specified table.
Tables.GetRelationships Gets the relationships among a set of tables.

Row operations

Name Description
Table.AlternateRows Keeps the initial offset then alternates taking and skipping the following rows.
Table.Combine Returns a table that is the result of merging a list of tables.
Table.FindText Returns all the rows that contain the given text in the table.
Table.First Returns the first row or a specified default value.
Table.FirstN Returns the first count rows specified.
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.
Table.InsertRows Inserts a list of rows into the table at the specified position.
Table.Last Returns the last row or a specified default value.
Table.LastN Returns the last specified number of rows.
Table.MatchesAllRows Indicates whether all the rows in the table meet the given condition.
Table.MatchesAnyRows Indicates whether any the rows in the table meet the given condition.
Table.Partition Partitions the table into a list of tables based on the number of groups and column specified.
Table.Range Returns the rows beginning at the specified offset.
Table.RemoveFirstN Returns a table with the specified number of rows removed from the table starting at the first row.
Table.RemoveLastN Returns a table with the specified number of rows removed from the table starting at the last row.
Table.RemoveRows Removes the specified number of rows.
Table.RemoveRowsWithErrors Returns a table with the rows removed from the input table that contain an error in at least one of the cells. If a columns list is specified, then only the cells in the specified columns are inspected for errors.
Table.Repeat Repeats the rows of the tables a specified number of times.
Table.ReplaceRows Replaces the specified range of rows with the provided row or rows.
Table.ReverseRows Returns a table with the rows in reverse order.
Table.SelectRows Selects the rows that meet the condition function.
Table.SelectRowsWithErrors Returns a table with only those rows of the input table that contain an error in at least one of the cells. If a columns list is specified, then only the cells in the specified columns are inspected for errors.
Table.SingleRow Returns a single row in the table.
Table.Skip Returns a table with the first specified number of rows skipped.
Table.SplitAt Returns a list containing the first count rows specified and the remaining rows.

Column operations

Name Description
Table.Column Returns a specified column of data from the table as a list.
Table.ColumnNames Returns the column names as a list.
Table.ColumnsOfType Returns a list with the names of the columns that match the specified types.
Table.DemoteHeaders Demotes the column headers to the first row of values.
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 or columns.
Table.Pivot Given a pair of columns representing attribute-value pairs, rotates the data in the attribute column into a column headings.
Table.PrefixColumns Returns a table where the columns have all been prefixed with the given text.
Table.PromoteHeaders Promotes the first row of values as the new column headers (that is, as column names).
Table.RemoveColumns Removes the specified columns.
Table.ReorderColumns Returns a table with the columns in the specified order.
Table.RenameColumns Returns a table with the columns renamed as specified.
Table.SelectColumns Returns a table with only the specified columns.
Table.TransformColumnNames Transforms column names by using the given function.
Table.Unpivot Translates a set of columns in a table into attribute-value pairs.
Table.UnpivotOtherColumns Translates all columns other than a specified set into attribute-value pairs.

Transformation

Name Description
Table.AddColumn Adds a column with the specified name. The value is computed using the specified selection function with each row taken as an input.
Table.AddFuzzyClusterColumn Adds a new column with representative values obtained by fuzzy grouping values of the specified column in the table.
Table.AddIndexColumn Appends a column with explicit position values.
Table.AddJoinColumn Performs a join between tables on supplied columns and produces the join result in a new column.
Table.AddKey Adds a key to a table.
Table.AggregateTableColumn Aggregates a column of tables into multiple columns in the containing table.
Table.CombineColumns Combines the specified columns into a new column using the specified combiner function.
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 Propagates the value of a previous cell to the null-valued cells below in the column.
Table.FillUp Propagates the value of a cell to the null-valued cells above in the column.
Table.FilterWithDataTable This function is intended for internal use only.
Table.FuzzyGroup Groups rows in the table based on fuzzy matching of keys.
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.Group Groups rows in the table that have the same key.
Table.Join Joins the rows from the two tables that match based on the given keys.
Table.Keys Returns the keys of the specified table.
Table.NestedJoin Performs a join between tables on supplied columns and produces the join result in 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 one value with another in the specified columns.
Table.Split Splits the specified table into a list of tables using the specified page size.
Table.SplitColumn Splits the specified columns into a set of additional columns using the specified splitter function.
Table.TransformColumns Transforms the values of one or more columns.
Table.TransformColumnTypes Applies type transformation(s) of the form { column, type } using a specific culture.
Table.TransformRows Transforms the rows of the table using the specified transform function.
Table.Transpose Makes columns into rows and rows into columns.

Membership

Name Description
Table.Contains Indicates whether the specified record appears as a row in the table.
Table.ContainsAll Indicates whether all of the specified records appear as rows in the table.
Table.ContainsAny Indicates whether any of the specified records appear as rows in the table.
Table.Distinct Removes duplicate rows from the table.
Table.IsDistinct Indicates whether the table contains only distinct rows (no duplicates).
Table.PositionOf Returns the position or positions of the row within the table.
Table.PositionOfAny Returns the position or positions of any of the specified rows within the table.
Table.RemoveMatchingRows Removes all occurrences of the specified rows from the table.
Table.ReplaceMatchingRows Replaces all the specified rows with the provided row or rows.

Ordering

Name Description
Table.AddRankColumn Appends a column with the ranking of one or more other columns.
Table.Max Returns the largest row or default value using the given criteria.
Table.MaxN Returns the largest row or rows using the given criteria.
Table.Min Returns the smallest row or a default value using the given criteria.
Table.MinN Returns the smallest row or rows using the given criteria.
Table.Sort Sorts the table using one or more column names and comparison criteria.

Other

Name Description
Table.Buffer Buffers a table in memory, isolating it from external changes during evaluation.
Table.StopFolding Prevents any downstream operations from being run against the original source of the data.

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.