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
#table	Creates a table value from columns and rows.
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.Combine	Returns a table that is the result of merging a list of tables.
Table.FromColumns	Creates a table from a list of columns and specified values.
Table.FromList	Converts a list into a table by applying the specified splitting function to each item in the list.
Table.FromPartitions	Returns a table that is the result of combining a set of partitioned tables.
Table.FromRecords	Converts a list of records into a table.
Table.FromRows	Creates a table from a list of row values and optional columns
Table.FromValue	Creates a table with a column from the provided value(s).
Table.Partition	Partitions the table into a list of tables based on the number of groups and column specified.
Table.PartitionValues	Returns information about how a table is partitioned.
Table.Split	Splits the specified table into a list of tables using the specified page size.
Table.SplitAt	Returns a list containing the first count rows specified and the remaining rows.
Table.ToColumns	Creates a list of nested lists of column values from a table.
Table.ToList	Converts a table into a list by applying the specified combining function to each row of values in the table.
Table.ToRecords	Converts a table to a list of records.
Table.ToRows	Creates a list of nested lists of row values from a table.

Information Functions

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

Column Operations

Add and Expand Columns

Function	Description
Table.AddColumn	Adds a column with the specified name
Table.AddIndexColumn	Appends a column with explicit position values.
Table.AddRankColumn	Appends a column with the ranking of one or more other columns.
Table.DuplicateColumn	Duplicates a column with the specified name
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.

Column Header Manipulation

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

Joining Columns

Function	Description
Table.AddFuzzyClusterColumn	Adds a new column with representative values obtained by fuzzy grouping values of the specified column in the table.
Table.AddJoinColumn	Performs a join between tables on supplied columns and produces the join result in a new column.
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.Join	Joins the rows from the two tables that match based on the given keys.
Table.NestedJoin	Performs a join between tables on supplied columns and produces the join result in a new column.

Selecting and Ordering Columns

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

Transform Columns

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

Row Operations

Adding or Removing Rows

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

Selecting and Ordering Rows

Function	Description
Table.FilterWithDataTable	This function is intended for internal use only.
Table.First	Returns the first row or a specified default value.
Table.FirstN	Returns the first count rows specified.
Table.FirstValue	Returns the first column of the first row of the table or a specified default value.
Table.Last	Returns the last row or a specified default value.
Table.LastN	Returns the last specified number of rows.
Table.Max	Returns the largest row or default value using the given criteria.
Table.MaxN	Returns the largest row(s) using the given criteria.
Table.Min	Returns the smallest row or a default value using the given criteria.
Table.MinN	Returns the smallest row(s) using the given criteria.
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.ReverseRows	Returns a table with the rows in reverse order.
Table.SelectRows	Selects the rows that meet the condition function.
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.SingleRow	Returns the single row in the table.
Table.Sort	Sorts the table using one or more column names and comparison criteria.

Transforming Rows

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

Other Table Functions

Function	Description
Table.Buffer	Buffers a table in memory, isolating it from external changes during evaluation.
Table.ConformToPageReader	This function is intended for internal use only.
Table.ReplaceRelationshipIdentity	This function is intended for internal use only.
Table.StopFolding	Prevents any downstream operations from being run against the original source of the data.
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).

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