Table Functions Overview

Updated on

Examine Table Functions in Power Query M Language, essential for creating and manipulating tables. This page provides a comprehensive list and descriptions of Table Functions available in the M Language.

Table of contents

Table Creation and Conversion

FunctionDescriptionInputOutput
#tableCreates a table value from columns and rows.AnyAny
ItemExpression.FromReturns the abstract syntax tree (AST) for the body of a function.FunctionRecord
ItemExpression.ItemAn abstract syntax tree (AST) node representing the item in an item expression.
RowExpression.ColumnReturns an abstract syntax tree (AST) that represents access to a column within a row expression.TextRecord
RowExpression.FromReturns the abstract syntax tree (AST) for the body of a function.FunctionRecord
RowExpression.RowAn abstract syntax tree (AST) node representing the row in a row expression.
Table.CombineReturns a table that is the result of merging a list of tables.ListTable
Table.FromColumnsCreates a table from a list of columns and specified values.ListTable
Table.FromListConverts a list into a table by applying the specified splitting function to each item in the list.ListTable
Table.FromPartitionsReturns a table that is the result of combining a set of partitioned tables.TextTable
Table.FromRecordsConverts a list of records into a table.ListTable
Table.FromRowsCreates a table from a list of row values and optional columnsListTable
Table.FromValueCreates a table with a column from the provided value(s).AnyTable
Table.PartitionPartitions the table into a list of tables based on the number of groups and column specified.TableList
Table.PartitionValuesReturns information about how a table is partitioned.TableTable
Table.SplitSplits the specified table into a list of tables using the specified page size.TableList
Table.SplitAtReturns a list containing the first count rows specified and the remaining rows.TableList
Table.ToColumnsCreates a list of nested lists of column values from a table.TableList
Table.ToListConverts a table into a list by applying the specified combining function to each row of values in the table.TableList
Table.ToRecordsConverts a table to a list of records.TableList
Table.ToRowsCreates a list of nested lists of row values from a table.TableList

Information Functions

FunctionDescriptionInputOutput
Table.ApproximateRowCountReturns the approximate number of rows in the table.TableNumber
Table.ColumnCountReturns the number of columns in the table.TableNumber
Table.ContainsIndicates whether the specified record appears as a row in the table.TableLogical
Table.ContainsAllIndicates whether all of the specified records appear as rows in the table.TableLogical
Table.ContainsAnyIndicates whether any of the specified records appear as rows in the table.TableLogical
Table.FindTextReturns all the rows that contain the given text in the table.TableTable
Table.HasColumnsIndicates whether the table contains the specified column(s).TableLogical
Table.IsDistinctIndicates whether the table contains only distinct rows (no duplicates).TableLogical
Table.IsEmptyIndicates whether the table contains any rows.TableLogical
Table.MatchesAllRowsIndicates whether all the rows in the table meet the given condition.TableLogical
Table.MatchesAnyRowsIndicates whether any the rows in the table meet the given condition.TableLogical
Table.PositionOfReturns the position or positions of the row within the table.TableAny
Table.PositionOfAnyReturns the position or positions of any of the specified rows within the table.TableAny
Table.ProfileReturns a profile of the columns of a table.TableTable
Table.RowCountReturns the number of rows in the table.TableNumber
Table.SchemaReturns a table containing a description of the columns (i.eTableTable
Tables.GetRelationshipsGets the relationships among a set of tables.TableTable

Column Operations

Add and Expand Columns

FunctionDescriptionInputOutput
Table.AddColumnAdds a column with the specified nameTableTable
Table.AddIndexColumnAppends a column with explicit position values.TableTable
Table.AddRankColumnAppends a column with the ranking of one or more other columns.TableTable
Table.DuplicateColumnDuplicates a column with the specified nameTableTable
Table.ExpandListColumnGiven a column of lists in a table, create a copy of a row for each value in its list.TableTable
Table.ExpandRecordColumnExpands a column of records into columns with each of the values.TableTable
Table.ExpandTableColumnExpands a column of records or a column of tables into multiple columns in the containing table.TableTable

Column Header Manipulation

FunctionDescriptionInputOutput
Table.ColumnNamesReturns the column names as a list.TableList
Table.ColumnsOfTypeReturns a list with the names of the columns that match the specified types.TableList
Table.DemoteHeadersDemotes the column headers to the first row of values.TableTable
Table.PromoteHeadersPromotes the first row of values as the new column headers (i.eTableTable
Table.TransformColumnNamesTransforms column names by using the given function.TableTable

Joining Columns

FunctionDescriptionInputOutput
Table.AddFuzzyClusterColumnAdds a new column with representative values obtained by fuzzy grouping values of the specified column in the table.TableTable
Table.AddJoinColumnPerforms a join between tables on supplied columns and produces the join result in a new column.TableTable
Table.FuzzyJoinJoins the rows from the two tables that fuzzy match based on the given keys.TableTable
Table.FuzzyNestedJoinPerforms a fuzzy join between tables on supplied columns and produces the join result in a new column.TableTable
Table.JoinJoins the rows from the two tables that match based on the given keys.TableTable
Table.NestedJoinPerforms a join between tables on supplied columns and produces the join result in a new column.TableTable

Selecting and Ordering Columns

FunctionDescriptionInputOutput
Table.ColumnReturns a specified column of data from the table as a list.TableList
Table.RemoveColumnsRemoves the specified columns.TableTable
Table.ReorderColumnsReturns a table with the columns in the specified order.TableTable
Table.SelectColumnsReturns a table with only the specified columns.TableTable

Transform Columns

FunctionDescriptionInputOutput
Table.AddKeyAdds a key to a table.TableTable
Table.CombineColumnsCombines the specified columns into a new column using the specified combiner function.TableTable
Table.CombineColumnsToRecordCombines 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.TableTable
Table.KeysReturns the keys of the specified table.TableList
Table.PivotGiven a pair of columns representing attribute-value pairs, rotates the data in the attribute column into a column headings.TableTable
Table.PrefixColumnsReturns a table where the columns have all been prefixed with the given text.TableTable
Table.ReplaceKeysReplaces the keys of the specified table.TableTable
Table.SplitColumnSplits the specified columns into a set of additional columns using the specified splitter function.TableTable
Table.TransformColumnTypesApplies type transformation(s) of the form { column, type } using a specific culture.TableTable
Table.TransformColumnsTransforms the values of one or more columns.TableTable
Table.TransformRowsTransforms the rows of the table using the specified transform function.TableList
Table.UnpivotTranslates a set of columns in a table into attribute-value pairs.TableTable
Table.UnpivotOtherColumnsTranslates all columns other than a specified set into attribute-value pairs.TableTable

Row Operations

Adding or Removing Rows

FunctionDescriptionInputOutput
Table.AlternateRowsKeeps the initial offset then alternates taking and skipping the following rows.TableTable
Table.DistinctRemoves duplicate rows from the table.TableTable
Table.InsertRowsInserts a list of rows into the table at the specified position.TableTable
Table.RangeReturns the rows beginning at the specified offset.TableTable
Table.RemoveFirstNReturns a table with the first count rows skipped.TableTable
Table.RemoveLastNReturns a table with the last N rows removed.TableTable
Table.RemoveMatchingRowsRemoves all occurrences of the specified rows from the table.TableTable
Table.RemoveRowsRemoves the specified number of rows.TableTable
Table.RepeatRepeats the rows of the tables a specified number of times.TableTable
Table.SkipReturns a table with the first count rows skipped.TableTable

Selecting and Ordering Rows

FunctionDescriptionInputOutput
Table.FilterWithDataTableThis function is intended for internal use only.TableAny
Table.FirstReturns the first row or a specified default value.TableAny
Table.FirstNReturns the first count rows specified.TableTable
Table.FirstValueReturns the first column of the first row of the table or a specified default value.TableAny
Table.LastReturns the last row or a specified default value.TableAny
Table.LastNReturns the last specified number of rows.TableTable
Table.MaxReturns the largest row or default value using the given criteria.TableAny
Table.MaxNReturns the largest row(s) using the given criteria.TableTable
Table.MinReturns the smallest row or a default value using the given criteria.TableAny
Table.MinNReturns the smallest row(s) using the given criteria.TableTable
Table.RemoveRowsWithErrorsReturns a table with the rows removed from the input table that contain an error in at least one of the cellsTableTable
Table.ReverseRowsReturns a table with the rows in reverse order.TableTable
Table.SelectRowsSelects the rows that meet the condition function.TableTable
Table.SelectRowsWithErrorsReturns a table with only those rows of the input table that contain an error in at least one of the cellsTableTable
Table.SingleRowReturns the single row in the table.TableRecord
Table.SortSorts the table using one or more column names and comparison criteria.TableTable

Transforming Rows

FunctionDescriptionInputOutput
Table.AggregateTableColumnAggregates a column of tables into multiple columns in the containing table.TableTable
Table.FillDownPropagates the value of a previous cell to the null-valued cells below in the column.TableTable
Table.FillUpPropagates the value of a cell to the null-valued cells above in the column.TableTable
Table.FuzzyGroupGroups rows in the table based on fuzzy matching of keys.TableTable
Table.GroupGroups rows in the table that have the same key.TableTable
Table.RenameColumnsApplies rename(s) of the form {old, new}.TableTable
Table.ReplaceErrorValuesReplaces the error values in the specified columns with the corresponding specified value.TableTable
Table.ReplaceMatchingRowsReplaces all the specified rows with the provided row(s).TableTable
Table.ReplaceRowsReplaces the specified range of rows with the provided row(s).TableTable
Table.ReplaceValueReplaces one value with another in the specified columns.TableTable
Table.TransposeMakes columns into rows and rows into columns.TableTable

Other Table Functions

FunctionDescriptionInputOutput
Table.BufferBuffers a table in memory, isolating it from external changes during evaluation.TableTable
Table.ConformToPageReaderThis function is intended for internal use only.TableTable
Table.ReplaceRelationshipIdentityThis function is intended for internal use only.AnyAny
Table.StopFoldingPrevents any downstream operations from being run against the original source of the data.TableTable
Table.ViewCreates or extends a table with user-defined handlers for query and action operations.TableTable
Table.ViewErrorCreates a modified error record which won’t trigger a fallback when thrown by a handler defined on a view (via Table.View).RecordRecord
Table.ViewFunctionCreates a function that can be intercepted by a handler defined on a view (via Table.View).FunctionFunction

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-functions