Table Functions

Table.RemoveColumns

Syntax Table.RemoveColumns(table as table, columns as any, optional missingField as nullable number) as table Description …

Table.IsDistinct

Syntax Table.IsDistinct(table as table, optional comparisonCriteria as any) as logical Description Indicates whether the table …

Table.ReplaceErrorValues

Syntax Table.ReplaceErrorValues(table as table, errorReplacement as list) as table Description Replaces the error values in …

Table.DemoteHeaders

Syntax Table.DemoteHeaders(table as table) as table Description Demotes the column headers (i.e. column names) to …

Table.FuzzyGroup

Syntax Table.FuzzyGroup(table as table, key as any, aggregatedColumns as list, optional options as nullable record) …

Tables.GetRelationships

Syntax Tables.GetRelationships(tables as table, optional dataColumn as nullable text) as table Description Gets the relationships …

Table.NestedJoin

Syntax Table.NestedJoin(table1 as table, key1 as any, table2 as any, key2 as any, newColumnName as …

Table.TransformColumnTypes

Syntax Table.TransformColumnTypes(table as table, typeTransformations as list, optional culture as nullable text) as table Description …

Table.Group

Syntax Table.Group(table as table, key as any, aggregatedColumns as list, optional groupKind as nullable number, …

Table.UnpivotOtherColumns

Syntax Table.UnpivotOtherColumns(table as table, pivotColumns as list, attributeColumn as text, valueColumn as text) as table …

Table.FromPartitions

Syntax Table.FromPartitions(partitionColumn as text, partitions as list, optional partitionColumnType as nullable type) as table Description …

RowExpression.From

Syntax RowExpression.From(function as function) as record Description Returns the AST for the body of function, …

Table.RemoveRowsWithErrors

Syntax Table.RemoveRowsWithErrors(table as table, optional columns as nullable list) as table Description Returns a table …

Table.RemoveLastN

Syntax Table.RemoveLastN(table as table, optional countOrCondition as any) as table Description Returns a table that …

Table.ExpandListColumn

Syntax Table.ExpandListColumn(table as table, column as text) as table Description Given a table, where a …

Table.ToList

Syntax Table.ToList(table as table, optional combiner as nullable function) as list Description Converts a table …

ExtraValues.Ignore

Syntax null Description If the splitter function returns more columns than the table expects, they …

Table.Profile

Syntax Table.Profile(table as table, optional additionalAggregates as nullable list) as table Description Returns a profile …

Table.RowCount

Syntax Table.RowCount(table as table) as number Description Returns the number of rows in the table. …

Table.Column

Syntax Table.Column(table as table, column as text) as list Description Returns the column of data …

JoinAlgorithm.LeftIndex

Syntax null Description JoinAlgorithm.LeftIndex Reference Data https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference Power Query M Functions / Table Functions / …

Table.ReplaceKeys

Syntax Table.ReplaceKeys(table as table, keys as list) as table Description Table.ReplaceKeys Reference Data https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference Power …

Table.FirstValue

Syntax Table.FirstValue(table as table, optional default as any) as any Description Returns the first column …

Table.PrefixColumns

Syntax Table.PrefixColumns(table as table, prefix as text) as table Description Returns a table where all …

Table.Combine

Syntax Table.Combine(tables as list, optional columns as any) as table Description Returns a table that …

Table.FillUp

Syntax Table.FillUp(table as table, columns as list) as table Description Returns a table from the …

Table.ViewFunction

Syntax Table.ViewFunction(function as function) as function Description Creates a view function based on function that …

JoinAlgorithm.PairwiseHash

Syntax null Description JoinAlgorithm.PairwiseHash Reference Data https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference Power Query M Functions / Table Functions / …

GroupKind.Global

Syntax GroupKind.Global Description null Reference Data https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference Power Query M Functions / Table Functions / …

JoinKind.RightAnti

Syntax null Description A possible value for the optional JoinKind parameter in Table.Join. A right …

Table.FromColumns

Syntax Table.FromColumns(lists as list, optional columns as any) as table Description Creates a table of …

Table.Keys

Syntax Table.Keys(table as table) as list Description Table.Keys Reference Data https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference Power Query M Functions …

ItemExpression.From

Syntax ItemExpression.From(function as function) as record Description Returns the AST for the body of function, …

MissingField.Ignore

Syntax null Description An optional parameter in record and table functions indicating that missing fields …

Table.Repeat

Syntax Table.Repeat(table as table, count as number) as table Description Returns a table with the …

Table.RemoveFirstN

Syntax Table.RemoveFirstN(table as table, optional countOrCondition as any) as table Description Returns a table that …

JoinKind.FullOuter

Syntax null Description A possible value for the optional JoinKind parameter in Table.Join. A full …

Table.MaxN

Syntax Table.MaxN(table as table, comparisonCriteria as any, countOrCondition as any) as table Description Returns the …

Table.FromRecords

Syntax Table.FromRecords(records as list, optional columns as any, optional missingField as nullable number) as table …

JoinKind.RightOuter

Syntax null Description A possible value for the optional JoinKind parameter in Table.Join. A right …

Table.Schema

Syntax Table.Schema(table as table) as table Description Returns a table describing the columns of table. …

GroupKind.Local

Syntax GroupKind.Local Description GroupKind.Local Reference Data https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference Power Query M Functions / Table Functions / …

Table.SelectRows

Syntax Table.SelectRows(table as table, condition as function) as table Description Returns a table of rows …

Table.Contains

Syntax Table.Contains(table as table, row as record, optional equationCriteria as any) as logical Description Indicates …

Table.Join

Syntax Table.Join(table1 as table, key1 as any, table2 as table, key2 as any, optional joinKind …

Table.PositionOf

Syntax Table.PositionOf(table as table, row as record, optional occurrence as any, optional equationCriteria as any) …

Table.Partition

Syntax Table.Partition(table as table, column as text, groups as number, hash as function) as list …

ItemExpression.Item

Syntax null Description An AST node representing the item in an item expression. Reference Data …

Table.HasColumns

Syntax Table.HasColumns(table as table, columns as any) as logical Description indicates whether the table contains …

Table.SelectRowsWithErrors

Syntax Table.SelectRowsWithErrors(table as table, optional columns as nullable list) as table Description Returns a table …

Table.ExpandRecordColumn

Syntax Table.ExpandRecordColumn(table as table, column as text, fieldNames as list, optional newColumnNames as nullable list) …

Table.LastN

Syntax Table.LastN(table as table, countOrCondition as any) as table Description Returns the last row(s) from …

Table.AlternateRows

Syntax Table.AlternateRows(table as table, offset as number, skip as number, take as number) as table …

Table.MatchesAllRows

Syntax Table.MatchesAllRows(table as table, condition as function) as logical Description Indicates whether all the rows …

JoinAlgorithm.RightIndex

Syntax null Description JoinAlgorithm.RightIndex Reference Data https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference Power Query M Functions / Table Functions / …

ExtraValues.List

Syntax null Description If the splitter function returns more columns than the table expects, they …

Table.Transpose

Syntax Table.Transpose(table as table, optional columns as any) as table Description Makes columns into rows …

Table.Pivot

Syntax Table.Pivot(table as table, pivotValues as list, attributeColumn as text, valueColumn as text, optional aggregationFunction …

RowExpression.Row

Syntax null Description An AST node representing the row in a row expression. Reference Data …

Table.InsertRows

Syntax Table.InsertRows(table as table, offset as number, rows as list) as table Description Returns a …

Table.FindText

Syntax Table.FindText(table as table, text as text) as table Description Returns the rows in the …

Table.PositionOfAny

Syntax Table.PositionOfAny(table as table, rows as list, optional occurrence as nullable number, optional equationCriteria as …

Table.CombineColumns

Syntax Table.CombineColumns(table as table, sourceColumns as list, combiner as function, column as text) as table …

Table.FuzzyJoin

Syntax Table.FuzzyJoin(table1 as table, key1 as any, table2 as table, key2 as any, optional joinKind …

MissingField.UseNull

Syntax null Description An optional parameter in record and table functions indicating that missing fields …

RowExpression.Column

Syntax RowExpression.Column(columnName as text) as record Description Returns an AST that represents access to column …

Table.Last

Syntax Table.Last(table as table, optional default as any) as any Description Returns the last row …

Table.IsEmpty

Syntax Table.IsEmpty(table as table) as logical Description Indicates whether the table contains any rows. Returns …

Table.PromoteHeaders

Syntax Table.PromoteHeaders(table as table, optional options as nullable record) as table Description Promotes the first …

JoinKind.Inner

Syntax null Description A possible value for the optional JoinKind parameter in Table.Join. The table …

Table.Skip

Syntax Table.Skip(table as table, optional countOrCondition as any) as table Description Returns a table that …

Table.FillDown

Syntax Table.FillDown(table as table, columns as list) as table Description Returns a table from the …

Table.ToColumns

Syntax Table.ToColumns(table as table) as list Description Creates a list of nested lists from the …

Table.AddIndexColumn

Syntax Table.AddIndexColumn(table as table, newColumnName as text, optional initialValue as nullable number, optional increment as …

Table.ReorderColumns

Syntax Table.ReorderColumns(table as table, columnOrder as list, optional missingField as nullable number) as table Description …

Table.TransformColumnNames

Syntax Table.TransformColumnNames(table as table, nameGenerator as function, optional options as nullable record) as table Description …

Table.ContainsAll

Syntax Table.ContainsAll(table as table, rows as list, optional equationCriteria as any) as logical Description Indicates …

Table.AddColumn

Syntax Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type) …

Table.FromList

Syntax Table.FromList(list as list, optional splitter as nullable function, optional columns as any, optional default …

Table.ReplaceValue

Syntax Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as …

Table.SelectColumns

Syntax Table.SelectColumns(table as table, columns as any, optional missingField as nullable number) as table Description …

Table.RemoveMatchingRows

Syntax Table.RemoveMatchingRows(table as table, rows as list, optional equationCriteria as any) as table Description Removes …

Table.ToRows

Syntax Table.ToRows(table as table) as list Description Creates a list of nested lists from the …

Table.MinN

Syntax Table.MinN(table as table, comparisonCriteria as any, countOrCondition as any) as table Description Returns the …

Table.ColumnsOfType

Syntax Table.ColumnsOfType(table as table, listOfTypes as list) as list Description Returns a list with the …

Table.TransformRows

Syntax Table.TransformRows(table as table, transform as function) as list Description Creates a table from table …

Table.ToRecords

Syntax Table.ToRecords(table as table) as list Description Converts a table, table, to a list of …

Table.Max

Syntax Table.Max(table as table, comparisonCriteria as any, optional default as any) as any Description Returns …

Table.DuplicateColumn

Syntax Table.DuplicateColumn(table as table, columnName as text, newColumnName as text, optional columnType as nullable type) …

Table.Sort

Syntax Table.Sort(table as table, comparisonCriteria as any) as table Description Sorts the table using the …

Table.TransformColumns

Syntax Table.TransformColumns(table as table, transformOperations as list, optional defaultTransformation as nullable function, optional missingField as …

Table.View

Syntax Table.View(table as nullable table, handlers as record) as table Description Returns a view of …

Table.Distinct

Syntax Table.Distinct(table as table, optional equationCriteria as any) as table Description Removes duplicate rows from …

Table.AddKey

Syntax Table.AddKey(table as table, columns as list, isPrimary as logical) as table Description Add a …

MissingField.Error

Syntax null Description An optional parameter in record and table functions indicating that missing fields …

Table.ReplaceRows

Syntax Table.ReplaceRows(table as table, offset as number, count as number, rows as list) as table …

Table.FirstN

Syntax Table.FirstN(table as table, countOrCondition as any) as table Description Returns the first row(s) of …

Table.ColumnNames

Syntax Table.ColumnNames(table as table) as list Description Returns the column names in the table table …

Table.ContainsAny

Syntax Table.ContainsAny(table as table, rows as list, optional equationCriteria as any) as logical Description Indicates …

Table.Split

Syntax Table.Split(table as table, pageSize as number) as list Description Splits table into a list …

JoinAlgorithm.SortMerge

Syntax null Description JoinAlgorithm.SortMerge Reference Data https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference Power Query M Functions / Table Functions / …

Table.AddJoinColumn

Syntax Table.AddJoinColumn(table1 as table, key1 as any, table2 as function, key2 as any, newColumnName as …

Table.RenameColumns

Syntax Table.RenameColumns(table as table, renames as list, optional missingField as nullable number) as table Description …

ExtraValues.Error

Syntax null Description If the splitter function returns more columns than the table expects, an …

Table.ColumnCount

Syntax Table.ColumnCount(table as table) as number Description Returns the number of columns in the table …

JoinSide.Left

Syntax null Description Specifies the left table of a join. Reference Data https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference Power Query …

Table.SplitColumn

Syntax Table.SplitColumn(table as table, sourceColumn as text, splitter as function, optional columnNamesOrNumber as any, optional …

Table.Buffer

Syntax Table.Buffer(table as table) as table Description Buffers a table in memory, isolating it from …

Table.FilterWithDataTable

Syntax Table.FilterWithDataTable(**table** as table, **dataTableIdentifier** as text) as any Description Table.FilterWithDataTable Reference Data https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference Power …

Table.FuzzyNestedJoin

Syntax Table.FuzzyNestedJoin(table1 as table, key1 as any, table2 as table, key2 as any, newColumnName as …

Table.Unpivot

Syntax Table.Unpivot(table as table, pivotColumns as list, attributeColumn as text, valueColumn as text) as table …

Table.SingleRow

Syntax Table.SingleRow(table as table) as record Description Returns the single row in the one row …

Table.RemoveRows

Syntax Table.RemoveRows(table as table, offset as number, optional count as nullable number) as table Description …

Table.AggregateTableColumn

Syntax Table.AggregateTableColumn(table as table, column as text, aggregations as list) as table Description Aggregates tables …

JoinKind.LeftOuter

Syntax null Description A possible value for the optional JoinKind parameter in Table.Join. A left …

Table.ExpandTableColumn

Syntax Table.ExpandTableColumn(table as table, column as text, columnNames as list, optional newColumnNames as nullable list) …

Table.Min

Syntax Table.Min(table as table, comparisonCriteria as any, optional default as any) as any Description Returns …

Table.ReplaceRelationshipIdentity

Syntax Table.ReplaceRelationshipIdentity(value as any, identity as text) as any Description Table.ReplaceRelationshipIdentity Reference Data https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference Power …

Table.ReplaceMatchingRows

Syntax Table.ReplaceMatchingRows(table as table, replacements as list, optional equationCriteria as any) as table Description Replaces …

Table.ReverseRows

Syntax Table.ReverseRows(table as table) as table Description Returns a table with the rows from the …

JoinSide.Right

Syntax null Description Specifies the right table of a join. Reference Data https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference Power Query …

Table.Range

Syntax Table.Range(table as table, offset as number, optional count as nullable number) as table Description …

JoinAlgorithm.RightHash

Syntax null Description JoinAlgorithm.RightHash Reference Data https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference Power Query M Functions / Table Functions / …

Table.MatchesAnyRows

Syntax Table.MatchesAnyRows(table as table, condition as function) as logical Description Indicates whether any the rows …

JoinAlgorithm.Dynamic

Syntax null Description JoinAlgorithm.Dynamic Reference Data https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference Power Query M Functions / Table Functions / …

JoinAlgorithm.LeftHash

Syntax null Description JoinAlgorithm.LeftHash Reference Data https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference Power Query M Functions / Table Functions / …

JoinKind.LeftAnti

Syntax null Description A possible value for the optional JoinKind parameter in Table.Join. A left …

Table.FromRows

Syntax Table.FromRows(rows as list, optional columns as any) as table Description Creates a table from …

Table.FromValue

Syntax Table.FromValue(value as any, optional options as nullable record) as table Description Creates a table …

Table.First

Syntax Table.First(table as table, optional default as any) as any Description Returns the first row …