Table functions
These functions create and manipulate table values.
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). |
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. |
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. |
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. |
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. |
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. |
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. |
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. |
Name | Description |
---|---|
Table.Buffer | Buffers a table into memory, isolating it from external changes during evaluation. |
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 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.
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
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
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 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 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.