Table of contents
TOC
Collapse the table of content
Expand the table of content

Table functions

Owen Duncan|Last Updated: 10/5/2018
|
1 Contributor

Table construction

FunctionDescription
ItemExpression.FromReturns the AST for the body of a function.
ItemExpression.ItemAn AST node representing the item in an item expression.
RowExpression.ColumnReturns an AST that represents access to a column within a row expression.
RowExpression.FromReturns the AST for the body of a function.
RowExpression.RowAn AST node representing the row in a row expression.
Table.FromColumnsReturns a table from a list containing nested lists with the column names and values.
Table.FromListConverts a list into a table by applying the specified splitting function to each item in the list.
Table.FromRecordsReturns a table from a list of records.
Table.FromRowsCreates a table from the list where each element of the list is a list that contains the column values for a single row.
Table.FromValueReturns a table with a column containing the provided value or list of values.
Table.SplitSplits the specified table into a list of tables using the specified page size.
Table.ViewCreates or extends a table with user-defined handlers for query and action operations.
Table.ViewFunctionCreates a function that can be intercepted by a handler defined on a view (via Table.View).

Conversions

FunctionDescription
Table.ToColumnsReturns a list of nested lists each representing a column of values in the input table.
Table.ToListReturns a table into a list by applying the specified combining function to each row of values in a table.
Table.ToRecordsReturns a list of records from an input table.
Table.ToRowsReturns a nested list of row values from an input table.

Information

FunctionDescription
Table.ColumnCountReturns the number of columns in a table.
Table.IsEmptyReturns true if the table does not contain any rows.
Table.ProfileReturns a profile of the columns of a table.
Table.RowCountReturns the number of rows in a table.
Table.SchemaReturns a table containing a description of the columns (i.e. the schema) of the specified table.
Tables.GetRelationshipsReturns the relationships among a set of tables.

Row operations

FunctionDescription
Table.AlternateRowsReturns a table containing an alternating pattern of the rows from a table.
Table.CombineReturns a table that is the result of merging a list of tables. The tables must all have the same row type structure.
Table.FindTextReturns a table containing only the rows that have the specified text within one of their cells or any part thereof.
Table.FirstReturns the first row from a table.
Table.FirstNReturns the first row(s) of a table, depending on the countOrCondition parameter.
Table.FirstValueReturns the first column of the first row of the table or a specified default value.
Table.FromPartitionsReturns 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.InsertRowsReturns 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.LastReturns the last row of a table.
Table.LastNReturns the last row(s) from a table, depending on the countOrCondition parameter.
Table.MatchesAllRowsReturns true if all of the rows in a table meet a condition.
Table.MatchesAnyRowsReturns true if any of the rows in a table meet a condition.
Table.PartitionPartitions 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.RangeReturns the specified number of rows from a table starting at an offset.
Table.RemoveFirstNReturns 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.RemoveLastNReturns 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.RemoveRowsReturns a table with the specified number of rows removed from the table starting at an offset.
Table.RemoveRowsWithErrorsReturns a table with all rows removed from the table that contain an error in at least one of the cells in a row.
Table.RepeatReturns a table containing the rows of the table repeated the count number of times.
Table.ReplaceRowsReturns a table where the rows beginning at an offset and continuing for count are replaced with the provided rows.
Table.ReverseRowsReturns a table with the rows in reverse order.
Table.SelectRowsReturns a table containing only the rows that match a condition.
Table.SelectRowsWithErrorsReturns a table with only the rows from table that contain an error in at least one of the cells in a row.
Table.SingleRowReturns a single row from a table.
Table.SkipReturns a table that does not contain the first row or rows of the table.

Column operations

FunctionDescription
Table.ColumnReturns the values from a column in a table.
Table.ColumnNamesReturns the names of columns from a table.
Table.ColumnsOfTypeReturns a list with the names of the columns that match the specified types.
Table.DemoteHeadersDemotes the header row down into the first row of a table.
Table.DuplicateColumnDuplicates a column with the specified name. Values and type are copied from the source column.
Table.HasColumnsReturns true if a table has the specified column or columns.
Table.PivotGiven 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.PrefixColumnsReturns a table where the columns have all been prefixed with a text value.
Table.PromoteHeadersPromotes the first row of the table into its header or column names.
Table.RemoveColumnsReturns a table without a specific column or columns.
Table.ReorderColumnsReturns a table with specific columns in an order relative to one another.
Table.RenameColumnsReturns a table with the columns renamed as specified.
Table.SelectColumnsReturns a table that contains only specific columns.
Table.TransformColumnNamesTransforms column names by using the given function.
Table.UnpivotGiven a list of table columns, transforms those columns into attribute-value pairs.
Table.UnpivotOtherColumnsTranslates 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 valuesDescription
JoinSide.LeftSpecifies the left table of a join.
JoinSide.RightSpecifies 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]  
  
})  
FunctionDescription
Table.AddColumnAdds a column named newColumnName to a table.
Table.AddIndexColumnReturns a table with a new column with a specific name that, for each row, contains an index of the row in the table.
Table.AddJoinColumnPerforms 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.AddKeyAdd a key to table.
Table.AggregateTableColumnAggregates tables nested in a specific column into multiple columns containing aggregate values for those tables.
Table.CombineColumnsTable.CombineColumns merges columns using a combiner function to produce a new column. Table.CombineColumns is the inverse of Table.SplitColumns.
Table.ExpandListColumnGiven a column of lists in a table, create a copy of a row for each value in its list.
Table.ExpandRecordColumnExpands a column of records into columns with each of the values.
Table.ExpandTableColumnExpands a column of records or a column of tables into multiple columns in the containing table.
Table.FillDownReplaces null values in the specified column or columns of the table with the most recent non-null value in the column.
Table.FillUpReturns 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.GroupGroups table rows by the values of key columns for each row.
Table.JoinJoins 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.KeysReturns a list of key column names from a table.
Table.NestedJoinJoins the rows of the tables based on the equality of the keys. The results are entered into a new column.
Table.ReplaceErrorValuesReplaces the error values in the specified columns with the corresponding specified value.
Table.ReplaceKeysReturns a new table with new key information set in the keys argument.
Table.ReplaceRelationshipIdentity
Table.ReplaceValueReplaces oldValue with newValue in specific columns of a table, using the provided replacer function, such as text.Replace or Value.Replace.
Table.SplitColumnReturns a new set of columns from a single column applying a splitter function to each value.
Table.TransformColumnsTransforms columns from a table using a function.
Table.TransformColumnTypesTransforms the column types from a table using a type.
Table.TransformRowsTransforms the rows from a table using a transform function.
Table.TransposeReturns 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  
    
FunctionDescription
Table.ContainsDetermines whether the a record appears as a row in the table.
Table.ContainsAllDetermines whether all of the specified records appear as rows in the table.
Table.ContainsAnyDetermines whether any of the specified records appear as rows in the table.
Table.DistinctRemoves duplicate rows from a table, ensuring that all remaining rows are distinct.
Table.IsDistinctDetermines whether a table contains only distinct rows.
Table.PositionOfDetermines the position or positions of a row within a table.
Table.PositionOfAnyDetermines the position or positions of any of the specified rows within the table.
Table.RemoveMatchingRowsRemoves all occurrences of rows from a table.
Table.ReplaceMatchingRowsReplaces 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  
  
])  
FunctionDescription
Table.MaxReturns the largest row or rows from a table using a comparisonCriteria.
Table.MaxNReturns the largest N rows from a table. After the rows are sorted, the countOrCondition parameter must be specified to further filter the result.
Table.MinReturns the smallest row or rows from a table using a comparisonCriteria.
Table.MinNReturns 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.SortSorts the rows in a table using a comparisonCriteria or a default ordering if one is not specified.

Other

FunctionDescription
Table.BufferBuffers 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.

© 2018 Microsoft