Table functions

Table construction

Function Description
ItemExpression.From Returns the AST for the body of a function.
ItemExpression.Item An AST node representing the item in an item expression.
RowExpression.Column Returns an AST that represents access to a column within a row expression.
RowExpression.From Returns the AST for the body of a function.
RowExpression.Row An 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.Split Splits the specified table into a list of tables using the specified page size.
Table.View Creates or extends a table with user-defined handlers for query and action operations.
Table.ViewFunction Creates a function that can be intercepted by a handler defined on a view (via Table.View).

Conversions

Function 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

Function Description
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

Function 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.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.

Column operations

Function 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 Returns true if a table has the specified column or columns.
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

Parameters for Group options

  • GroupKind.Global = 0;

  • GroupKind.Local = 1;

Parameters for Join kinds

  • JoinKind.Inner = 0;

  • JoinKind.LeftOuter = 1;

  • JoinKind.RightOuter = 2;

  • JoinKind.FullOuter = 3;

  • JoinKind.LeftAnti = 4;

  • JoinKind.RightAnti = 5

Join Algorithm

The following JoinAlgorithm values can be specified to Table.Join

  • JoinAlgorithm.Dynamic        0,  
    
  • JoinAlgorithm.PairwiseHash   1,  
    
  • JoinAlgorithm.SortMerge      2,  
    
  • JoinAlgorithm.LeftHash       3,  
    
  • JoinAlgorithm.RightHash      4,  
    
  • JoinAlgorithm.LeftIndex      5,  
    
  • JoinAlgorithm.RightIndex     6,  
    
Parameter values Description
JoinSide.Left Specifies the left table of a join.
JoinSide.Right Specifies the right table of a join.

Example data

The following tables are used by the examples in this section.

Customers table

Customers = Table.FromRecords({  

  [CustomerID = 1, Name = "Bob", Phone = "123-4567"],  

  [CustomerID = 2, Name = "Jim", Phone = "987-6543"],  

  [CustomerID = 3, Name = "Paul", Phone = "543-7890"],  

  [CustomerID = 4, Name = "Ringo", Phone = "232-1550"]  

}  

Orders table

Orders = Table.FromRecords({  

  [OrderID = 1, CustomerID = 1, Item = "Fishing rod", Price = 100.0],  

  [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],  

  [OrderID = 3, CustomerID = 2, Item = "Fishing net", Price = 25.0],  

  [OrderID = 4, CustomerID = 3, Item = "Fish tazer", Price = 200.0],  

  [OrderID = 5, CustomerID = 3, Item = "Bandaids", Price = 2.0],  

  [OrderID = 6, CustomerID = 1, Item = "Tackle box", Price = 20.0],  

  [OrderID = 7, CustomerID = 5, Item = "Bait", Price = 3.25],  

  [OrderID = 8, CustomerID = 5, Item = "Fishing Rod", Price = 100.0],  

  [OrderID = 9, CustomerID = 6, Item = "Bait", Price = 3.25]  

})  
Function Description
Table.AddColumn Adds a column named newColumnName to a 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 Add 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.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
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 a list of key column names from a 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 Returns a new table with new key information set in the keys argument.
Table.ReplaceRelationshipIdentity
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 columns from a table using a function.
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

Parameters for membership checks

Occurrence specification

  • Occurrence.First  = 0  
    
  • Occurrence.Last   = 1  
    
  • Occurrence.All    = 2  
    
Function 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

Example data

The following tables are used by the examples in this section.

Employees table

Employees = Table.FromRecords(  

    {[Name="Bill",   Level=7,  Salary=100000],  

     [Name="Barb",   Level=8,  Salary=150000],  

     [Name="Andrew", Level=6,  Salary=85000],  

     [Name="Nikki",  Level=5,  Salary=75000],  

     [Name="Margo",  Level=3,  Salary=45000],  

     [Name="Jeff",   Level=10, Salary=200000]},  

type table [  

    Name = text,  

    Level = number,  

    Salary = number  

])  
Function 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.Sort Sorts the rows in a table using a comparisonCriteria or a default ordering if one is not specified.

Other

Function 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. See sort order in the parameter values section above.

  • To compute a key to be used for sorting, a function of 1 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 2 arguments can be used that returns -1, 0, or 1 given the relationship between the left and right inputs. Value.Compare is a method that can be used to delegate this logic.

For examples, see description of Table.Sort.

Count or Condition critieria

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.

See Table.FirstN or Table.MaxN.

Handling of extra values

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

ExtraValues.List = 0  

ExtraValues.Error = 1  

ExtraValues.Ignore = 2  

For more information, see Table.FromList.

Missing column handling

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

MissingField.Error = 0;  

MissingField.Ignore = 1;  

MissingField.UseNull = 2;  

This is used in column or transformation operations. For Examples, see Table.TransformColumns.

Sort Order

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

Order.Ascending = 0  

    Order.Descending = 1  

Equation criteria

Equation criteria for tables can be specified as either a

  • A function value that is either

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

    • A comparer function that is used to specify the kind of comparison to apply. Built in comparer functions can be specified, see section for Comparer functions.

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

For examples, look at description for Table.Distinct.