Table Functions Overview

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
• Information Functions
• Column Operations
  • Add and Expand Columns
  • Column Header Manipulation
  • Joining Columns
  • Selecting and Ordering Columns
  • Transform Columns
• Row Operations
  • Adding or Removing Rows
  • Selecting and Ordering Rows
  • Transforming Rows
• Other Table Functions

Table Creation and Conversion

Function	Description	Input	Output
#table	Creates a table value from columns and rows.	Any	Any
ItemExpression.From	Returns the abstract syntax tree (AST) for the body of a function.	Function	Record
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.	Text	Record
RowExpression.From	Returns the abstract syntax tree (AST) for the body of a function.	Function	Record
RowExpression.Row	An abstract syntax tree (AST) node representing the row in a row expression.
Table.Combine	Returns a table that is the result of merging a list of tables.	List	Table
Table.FromColumns	Creates a table from a list of columns and specified values.	List	Table
Table.FromList	Converts a list into a table by applying the specified splitting function to each item in the list.	List	Table
Table.FromPartitions	Returns a table that is the result of combining a set of partitioned tables.	Text	Table
Table.FromRecords	Converts a list of records into a table.	List	Table
Table.FromRows	Creates a table from a list of row values and optional columns	List	Table
Table.FromValue	Creates a table with a column from the provided value(s).	Any	Table
Table.Partition	Partitions the table into a list of tables based on the number of groups and column specified.	Table	List
Table.PartitionValues	Returns information about how a table is partitioned.	Table	Table
Table.Split	Splits the specified table into a list of tables using the specified page size.	Table	List
Table.SplitAt	Returns a list containing the first count rows specified and the remaining rows.	Table	List
Table.ToColumns	Creates a list of nested lists of column values from a table.	Table	List
Table.ToList	Converts a table into a list by applying the specified combining function to each row of values in the table.	Table	List
Table.ToRecords	Converts a table to a list of records.	Table	List
Table.ToRows	Creates a list of nested lists of row values from a table.	Table	List

Information Functions

Function	Description	Input	Output
Table.ApproximateRowCount	Returns the approximate number of rows in the table.	Table	Number
Table.ColumnCount	Returns the number of columns in the table.	Table	Number
Table.Contains	Indicates whether the specified record appears as a row in the table.	Table	Logical
Table.ContainsAll	Indicates whether all of the specified records appear as rows in the table.	Table	Logical
Table.ContainsAny	Indicates whether any of the specified records appear as rows in the table.	Table	Logical
Table.FindText	Returns all the rows that contain the given text in the table.	Table	Table
Table.HasColumns	Indicates whether the table contains the specified column(s).	Table	Logical
Table.IsDistinct	Indicates whether the table contains only distinct rows (no duplicates).	Table	Logical
Table.IsEmpty	Indicates whether the table contains any rows.	Table	Logical
Table.MatchesAllRows	Indicates whether all the rows in the table meet the given condition.	Table	Logical
Table.MatchesAnyRows	Indicates whether any the rows in the table meet the given condition.	Table	Logical
Table.PositionOf	Returns the position or positions of the row within the table.	Table	Any
Table.PositionOfAny	Returns the position or positions of any of the specified rows within the table.	Table	Any
Table.Profile	Returns a profile of the columns of a table.	Table	Table
Table.RowCount	Returns the number of rows in the table.	Table	Number
Table.Schema	Returns a table containing a description of the columns (i.e	Table	Table
Tables.GetRelationships	Gets the relationships among a set of tables.	Table	Table

Column Operations

Add and Expand Columns

Function	Description	Input	Output
Table.AddColumn	Adds a column with the specified name	Table	Table
Table.AddIndexColumn	Appends a column with explicit position values.	Table	Table
Table.AddRankColumn	Appends a column with the ranking of one or more other columns.	Table	Table
Table.DuplicateColumn	Duplicates a column with the specified name	Table	Table
Table.ExpandListColumn	Given a column of lists in a table, create a copy of a row for each value in its list.	Table	Table
Table.ExpandRecordColumn	Expands a column of records into columns with each of the values.	Table	Table
Table.ExpandTableColumn	Expands a column of records or a column of tables into multiple columns in the containing table.	Table	Table

Column Header Manipulation

Function	Description	Input	Output
Table.ColumnNames	Returns the column names as a list.	Table	List
Table.ColumnsOfType	Returns a list with the names of the columns that match the specified types.	Table	List
Table.DemoteHeaders	Demotes the column headers to the first row of values.	Table	Table
Table.PromoteHeaders	Promotes the first row of values as the new column headers (i.e	Table	Table
Table.TransformColumnNames	Transforms column names by using the given function.	Table	Table

Joining Columns

Function	Description	Input	Output
Table.AddFuzzyClusterColumn	Adds a new column with representative values obtained by fuzzy grouping values of the specified column in the table.	Table	Table
Table.AddJoinColumn	Performs a join between tables on supplied columns and produces the join result in a new column.	Table	Table
Table.FuzzyJoin	Joins the rows from the two tables that fuzzy match based on the given keys.	Table	Table
Table.FuzzyNestedJoin	Performs a fuzzy join between tables on supplied columns and produces the join result in a new column.	Table	Table
Table.Join	Joins the rows from the two tables that match based on the given keys.	Table	Table
Table.NestedJoin	Performs a join between tables on supplied columns and produces the join result in a new column.	Table	Table

Selecting and Ordering Columns

Function	Description	Input	Output
Table.Column	Returns a specified column of data from the table as a list.	Table	List
Table.RemoveColumns	Removes the specified columns.	Table	Table
Table.ReorderColumns	Returns a table with the columns in the specified order.	Table	Table
Table.SelectColumns	Returns a table with only the specified columns.	Table	Table

Transform Columns

Function	Description	Input	Output
Table.AddKey	Adds a key to a table.	Table	Table
Table.CombineColumns	Combines the specified columns into a new column using the specified combiner function.	Table	Table
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	Table
Table.Keys	Returns the keys of the specified table.	Table	List
Table.Pivot	Given a pair of columns representing attribute-value pairs, rotates the data in the attribute column into a column headings.	Table	Table
Table.PrefixColumns	Returns a table where the columns have all been prefixed with the given text.	Table	Table
Table.ReplaceKeys	Replaces the keys of the specified table.	Table	Table
Table.SplitColumn	Splits the specified columns into a set of additional columns using the specified splitter function.	Table	Table
Table.TransformColumnTypes	Applies type transformation(s) of the form { column, type } using a specific culture.	Table	Table
Table.TransformColumns	Transforms the values of one or more columns.	Table	Table
Table.TransformRows	Transforms the rows of the table using the specified transform function.	Table	List
Table.Unpivot	Translates a set of columns in a table into attribute-value pairs.	Table	Table
Table.UnpivotOtherColumns	Translates all columns other than a specified set into attribute-value pairs.	Table	Table

Row Operations

Adding or Removing Rows

Function	Description	Input	Output
Table.AlternateRows	Keeps the initial offset then alternates taking and skipping the following rows.	Table	Table
Table.Distinct	Removes duplicate rows from the table.	Table	Table
Table.InsertRows	Inserts a list of rows into the table at the specified position.	Table	Table
Table.Range	Returns the rows beginning at the specified offset.	Table	Table
Table.RemoveFirstN	Returns a table with the first count rows skipped.	Table	Table
Table.RemoveLastN	Returns a table with the last N rows removed.	Table	Table
Table.RemoveMatchingRows	Removes all occurrences of the specified rows from the table.	Table	Table
Table.RemoveRows	Removes the specified number of rows.	Table	Table
Table.Repeat	Repeats the rows of the tables a specified number of times.	Table	Table
Table.Skip	Returns a table with the first count rows skipped.	Table	Table

Selecting and Ordering Rows

Function	Description	Input	Output
Table.FilterWithDataTable	This function is intended for internal use only.	Table	Any
Table.First	Returns the first row or a specified default value.	Table	Any
Table.FirstN	Returns the first count rows specified.	Table	Table
Table.FirstValue	Returns the first column of the first row of the table or a specified default value.	Table	Any
Table.Last	Returns the last row or a specified default value.	Table	Any
Table.LastN	Returns the last specified number of rows.	Table	Table
Table.Max	Returns the largest row or default value using the given criteria.	Table	Any
Table.MaxN	Returns the largest row(s) using the given criteria.	Table	Table
Table.Min	Returns the smallest row or a default value using the given criteria.	Table	Any
Table.MinN	Returns the smallest row(s) using the given criteria.	Table	Table
Table.RemoveRowsWithErrors	Returns a table with the rows removed from the input table that contain an error in at least one of the cells	Table	Table
Table.ReverseRows	Returns a table with the rows in reverse order.	Table	Table
Table.SelectRows	Selects the rows that meet the condition function.	Table	Table
Table.SelectRowsWithErrors	Returns a table with only those rows of the input table that contain an error in at least one of the cells	Table	Table
Table.SingleRow	Returns the single row in the table.	Table	Record
Table.Sort	Sorts the table using one or more column names and comparison criteria.	Table	Table

Transforming Rows

Function	Description	Input	Output
Table.AggregateTableColumn	Aggregates a column of tables into multiple columns in the containing table.	Table	Table
Table.FillDown	Propagates the value of a previous cell to the null-valued cells below in the column.	Table	Table
Table.FillUp	Propagates the value of a cell to the null-valued cells above in the column.	Table	Table
Table.FuzzyGroup	Groups rows in the table based on fuzzy matching of keys.	Table	Table
Table.Group	Groups rows in the table that have the same key.	Table	Table
Table.RenameColumns	Applies rename(s) of the form {old, new}.	Table	Table
Table.ReplaceErrorValues	Replaces the error values in the specified columns with the corresponding specified value.	Table	Table
Table.ReplaceMatchingRows	Replaces all the specified rows with the provided row(s).	Table	Table
Table.ReplaceRows	Replaces the specified range of rows with the provided row(s).	Table	Table
Table.ReplaceValue	Replaces one value with another in the specified columns.	Table	Table
Table.Transpose	Makes columns into rows and rows into columns.	Table	Table

Other Table Functions

Function	Description	Input	Output
Table.Buffer	Buffers a table in memory, isolating it from external changes during evaluation.	Table	Table
Table.ConformToPageReader	This function is intended for internal use only.	Table	Table
Table.ReplaceRelationshipIdentity	This function is intended for internal use only.	Any	Any
Table.StopFolding	Prevents any downstream operations from being run against the original source of the data.	Table	Table
Table.View	Creates or extends a table with user-defined handlers for query and action operations.	Table	Table
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).	Record	Record
Table.ViewFunction	Creates a function that can be intercepted by a handler defined on a view (via Table.View).	Function	Function

Last update: November 24, 2023 | Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-functions
© 2023 BI Gorilla. All rights reserved. Content derived from Microsoft documentation is property of Microsoft Corp.