Table functions
Table functions functions for Power Query M language.
Functions
| Function | Description |
|---|---|
| #table | Generates a table structure by extracting values from designated columns and rows. The columns can consist of a set of column names, a table data type, a specified number of columns, or a null value. ... |
| ItemExpression.From | This function retrieves the abstract syntax tree (AST) for the body of a function, which is then normalized into an item expression. The function must be a lambda with only one argument. All mentions ... |
| ItemExpression.Item | No description available |
| RowExpression.Column | This function generates an abstract syntax tree (AST) that indicates how to access the specified column (columnName) within a row expression. |
| RowExpression.From | This function returns the abstract syntax tree (AST) for the body of a function, which is normalized into a row expression. The function must be a lambda with only one argument. All references to the ... |
| RowExpression.Row | No description available |
| Table.AddColumn | A new column named newColumnName is created in the table table. The values for this column are determined by the specified selection function columnGenerator, which uses each row as an input for compu... |
| Table.AddFuzzyClusterColumn | Introduces a new column, newColumnName, to a table containing representative values from the columnName column. These representatives are determined through a fuzzy matching process for each row. An ... |
| Table.AddIndexColumn | Adds a new column to the table with the specified name newColumnName, placing it in a specific position. You can also provide an initial value (initialValue) for the index and specify the increment (i... |
| Table.AddJoinColumn | Merges the rows of table1 with the rows of table2 using the key columns selected by key1 (for table1) and key2 (for table2) to determine equality. The output is stored in a new column called newColumn... |
| Table.AddKey | A new key is inserted into a table. The 'columns' parameter should contain the list of column names that will form the key, while the 'isPrimary' parameter indicates if it is a primary key. |
| Table.AddRankColumn | Adds a new column called newColumnName to the table based on the comparisonCriteria provided for ranking one or more existing columns. Advanced users can utilize the RankKind option in options for sel... |
| Table.AggregateTableColumn | Aggregate tables are combined into multiple columns that contain aggregate values for the tables. The "aggregations" function is utilized to identify the columns containing the tables to aggregate, th... |
| Table.AlternateRows | Maintains the starting position and alternatively selects and omits consecutive rows in the specified table. Table: The original data table provided as input. Offset: Represents the initial number o... |
| Table.ApproximateRowCount | This function will provide an estimate of the total number of rows in the table. If the data source does not support approximation, an error message will be returned instead. |
| Table.Buffer | This function stores a table in memory, protecting it from external modifications during assessment. The buffering process is not deep, as it only enforces the evaluation of individual cell values whi... |
| Table.Column | This function retrieves a specific column of data from a designated table and displays it as a list. |
| Table.ColumnCount | This function will retrieve the total count of columns present in the specified table. |
| Table.ColumnNames | Returns a list of text that represents the names of the columns in the specified table. |
| Table.ColumnsOfType | This function will generate a list of column names from the given table that meet the criteria specified in the list of types. |
| Table.Combine | This function returns a table that is created by combining multiple tables together. The structure of the resulting table will be based on the columns specified or a combination of the input table str... |
| Table.CombineColumns | Table.CombineColumns is a function that merges multiple columns into one by using a combiner function. This results in the creation of a new column. It is important to note that Table.CombineColumns w... |
| Table.CombineColumnsToRecord | The process involves merging the designated columns from a table into a new column that contains records, named newColumnName. Each record includes field names and values that match the corresponding ... |
| Table.ConformToPageReader | This function is designed to be used only within the organization. |
| Table.Contains | This function determines if a specific row, or record, is present in the table. Users can choose to include an optional parameter called equationCriteria to specify how the rows should be compared. |
| Table.ContainsAll | The function determines if all the records listed in the rows are present in the table as rows. Users can also include equationCriteria as an optional parameter to manage row comparisons in the table. |
| Table.ContainsAny | Shows if any of the specified records in the list of rows appear in the table. You can specify an optional equationCriteria parameter to determine how the rows in the table should be compared. |
| Table.DemoteHeaders | Moves the column headers, also known as column names, down to the first row of values. The initial column names are "Column1", "Column2", and similar. |
| Table.Distinct | This function removes duplicate rows from a table. You can specify which columns to use for checking duplicates using the optional parameter equationCriteria. If equationCriteria is not provided, all ... |
| Table.DuplicateColumn | Copy the column with the name "columnName" to the table named "table." The values and data type for the new column, which will be named "newColumnName," will be an exact replica of the original column... |
| Table.ExpandListColumn | When presented with a table containing a column of values, this process involves breaking down the list into individual rows for each value. The values in the remaining columns are replicated in every... |
| Table.ExpandRecordColumn | Based on the input table's column of records, a new table will be generated with individual columns representing each field in the record. If desired, unique names for the columns in the new table can... |
| Table.ExpandTableColumn | The function is designed to split tables contained in a specific column into multiple rows and columns. By specifying the columnNames parameter, users can indicate which columns should be expanded fro... |
| Table.FillDown | Returns a table extracted from the specified table, in which the values from cells above are filled into any null cells below in the designated columns. |
| Table.FillUp | This function retrieves a table from a specified source table. It fills in any null values in the specified columns by copying the value from the following non-null cell in that column. |
| Table.FilterWithDataTable | This function is designed to be used only within the organization. |
| Table.FindText | Returns a table that includes only the rows containing the specified text in any part of their cells. |
| Table.First | This function retrieves the initial row from the table. If the table is empty, the function returns an optional default value, which is specified by the user. |
| Table.FirstN | This function retrieves the initial row(s) from the specified table based on the countOrCondition value. When countOrCondition is a numerical value, it returns that quantity of rows from the top of th... |
| Table.FirstValue | This function retrieves the initial column of the initial row from the specified table. If no value is found, it will return a specified default value. |
| Table.FromColumns | Generates a table with columns of a specified type using a list of nested lists that include column names and values. In cases where columns have differing numbers of values, any missing values will b... |
| Table.FromList | Transforms a list into a table using a specified splitting function called "splitter" for each item in the list. By default, the list is considered to be a collection of text values separated by comma... |
| Table.FromPartitions | This function produces a table by merging multiple partitioned tables. The partition column to be added is specified by partitionColumn. By default, the column type is set to any, but it can be custom... |
| Table.FromRecords | Organizing and formatting individual records into a structured table format. |
| Table.FromRows | Generates a table by organizing the rows list into inner lists, with each inner list representing the column values of a single row. It is possible to specify column names, the table type, or the numb... |
| Table.FromValue | Generates a table that includes a column with the specified value or list of values. Users have the option to include a parameter, called options, which can be used to adjust the following settings: ... |
| Table.FuzzyGroup | This function groups the rows of a table by matching values in the specified column, known as the key, for each row with a fuzzy logic approach. For each group, a record is created with the key column... |
| Table.FuzzyJoin | The operation joins the rows of table1 with the rows of table2 using a fuzzy matching algorithm to compare the values of the key columns specified by key1 (for table1) and key2 (for table2). Fuzzy mat... |
| Table.FuzzyNestedJoin | The operation merges the rows of two tables, table1 and table2, based on a fuzzy comparison of values in the key columns specified by key1 (for table1) and key2 (for table2). The merged results are di... |
| Table.Group | This function groups the rows of a table based on key columns specified by the user. The key can be a single column name or a list of column names. For each group, a record is created that includes th... |
| Table.HasColumns | This function checks if the specified columns are present in the table. It will return true if the column(s) are found, and false if they are not. |
| Table.InsertRows | The function retrieves a table containing rows that have been inserted into the specified position, offset. It is important to note that each column in the row being inserted must have matching column... |
| Table.IsDistinct | This function checks if a table has any duplicate rows. It will return true if the rows are distinct, and false if there are duplicates. You can also specify which columns to check for duplication usi... |
| Table.IsEmpty | This function checks if there are any rows in the table. It will return true if the table is empty and false if there are rows present. |
| Table.Join | The function combines the rows of table1 with the rows of table2 by matching the values of the key columns specified by key1 (for table1) and key2 (for table2). By default, it performs an inner join, ... |
| Table.Keys | Returns the identifiers of the designated table. |
| Table.Last | This function retrieves the final row from the table. If the table has no rows, it will return the default value provided. |
| Table.LastN | This function retrieves the final row(s) from the specified table, based on the input value of countOrCondition: - If countOrCondition is a numerical value, then that number of rows will be fetched s... |
| Table.MatchesAllRows | This function checks if all rows in the table meet a specific condition. It will return true if all rows meet the condition, and false if at least one row does not meet the condition. |
| Table.MatchesAnyRows | This function determines if there are any rows in the table that meet the specified condition. It will return true if at least one row matches the condition, and false if no rows match. |
| Table.Max | This function retrieves the maximum row from the table based on the comparison criteria provided. If the table does not contain any rows, it will return the specified default value, if provided. |
| Table.MaxN | This function retrieves the row(s) with the highest value in the table based on the comparisonCriteria provided. The rows will be sorted before the result is further filtered using the countOrConditio... |
| Table.Min | This function retrieves the row with the smallest value in the table, based on the comparisonCriteria provided. If the table contains no rows, an optional default value can be specified to be returned... |
| Table.MinN | The function retrieves the smallest row(s) from a table based on the comparison criteria provided. Upon sorting the rows, the countOrCondition parameter is required to refine the results. It should be... |
| Table.NestedJoin | Merge the rows of table1 with the rows of table2 using the values of key1 (for table1) and key2 (for table2) to determine equality. The combined results will be placed in a new column called newColumn... |
| Table.Partition | The process involves dividing the table into multiple groups of tables based on the column value and a hash function. The hash function is used to calculate a hash value for each row in the column. Th... |
| Table.PartitionValues | This function retrieves data regarding the partitioning of a table. The output is a table that contains a column for each partitioning column in the original table. Each row corresponds to a partition... |
| Table.Pivot | When provided with a table and attribute column containing pivot values, the program will generate new columns for each pivot value and populate them with corresponding values from the value column. I... |
| Table.PositionOf | This function retrieves the position of the first instance of the specified row in the input table. If the row is not found, it returns -1. Parameters: - table: The table in which to search for the ... |
| Table.PositionOfAny | This function retrieves the position(s) of the first instance of the specified rows in the table. It returns -1 if the rows are not found in the table. Parameters include: - table: the input table - r... |
| Table.PrefixColumns | Generates a new table that contains all column names of the provided table with the specified prefix added before each name, followed by a period. The format of the new column names will be prefix.Col... |
| Table.Profile | The function retrieves a profile containing various statistical information for the columns in a given table. This information includes the minimum value, maximum value, average, standard deviation, c... |
| Table.PromoteHeaders | The function promotes the values in the first row to be used as column headers. Only text or number values are allowed as column headers by default. There are two valid options: 1. PromoteAllScalars... |
| Table.Range | This function retrieves rows from a table starting at a specified position. There is an optional parameter called count that determines the number of rows to be retrieved. If count is not provided, by... |
| Table.RemoveColumns | This function eliminates the columns indicated from the given table. If the specified column does not exist, an error is triggered unless the optional parameter "missingField" is used to specify an al... |
| Table.RemoveFirstN | This function retrieves a table without the initial set number of rows specified by the 'countOrCondition' parameter from the input table 'table'. The number of rows excluded is determined by the opti... |
| Table.RemoveLastN | This function retrieves a table from the input table without including the last countOrCondition rows. The amount of rows removed is based on the optional parameter countOrCondition. If countOrConditi... |
| Table.RemoveMatchingRows | Deletes all instances of the specified rows from the table. A parameter called equationCriteria can be optionally included to determine how the rows are compared in the table. |
| Table.RemoveRows | This function eliminates a specified number of rows from the table, starting from the specified offset. If no count parameter is given, a default count of 1 row will be removed. |
| Table.RemoveRowsWithErrors | The function will provide a table that excludes any rows from the original table that have errors in any of the cells. If a list of columns is given, the function will only check for errors in the cel... |
| Table.RenameColumns | This function is used to rename columns in a specified table. To perform a rename, you need to provide a list of two values for each replacement operation, which includes the old column name and the n... |
| Table.ReorderColumns | This function retrieves a table from the input table, arranging the columns according to the specified column order. Columns that are not mentioned in the list will remain in their original order. An ... |
| Table.Repeat | Returns a table containing rows from the original input table that have been duplicated a specified number of times. |
| Table.ReplaceErrorValues | Substitute the inaccurate values in the designated columns of the table with the updated values found in the errorReplacement list. The errorReplacement list should be in the format of {{column1, valu... |
| Table.ReplaceKeys | Updates the keys of the specified table. |
| Table.ReplaceMatchingRows | This function will update all the designated rows in the table with the rows provided. The rows to be replaced and the replacement values are indicated in the 'replacements' parameter, using the forma... |
| Table.ReplaceRelationshipIdentity | This function is designed to be used only within the organization. |
| Table.ReplaceRows | Substitutes a designated quantity of rows, known as count, in the input table with the provided rows, starting after the offset. The rows parameter consists of a collection of records. table: The tabl... |
| Table.ReplaceValue | Updates the existing value with a new value in designated columns of a table by utilizing the designated replacer function, such as text.Replace or Value.Replace. |
| Table.ReverseRows | This function produces a table that displays the rows of the input table in reversed order. |
| Table.RowCount | The function retrieves the total count of rows present in the specified table. |
| Table.Schema | The function will produce a table that outlines the various columns within a specified table. Each row in the table will provide information on the characteristics of a specific column within the tabl... |
| Table.SelectColumns | This function retrieves a table containing only the columns specified by the user. - Table: The table that the user wants to extract columns from. - Columns: A list of specific columns from the table... |
| Table.SelectRows | Retrieves a list of rows from the table that meet the specified criteria. |
| Table.SelectRowsWithErrors | The function retrieves a table consisting of rows from the original table that have at least one erroneous cell. If a list of columns is provided, only the cells in those specified columns will be che... |
| Table.SingleRow | This function retrieves the only row in a table with one row. If the table does not have exactly one row, an error will be triggered. |
| Table.Skip | This function returns a table without the first specified number of rows, countOrCondition, from a given table called table. The number of rows skipped is determined by the optional parameter countOrC... |
| Table.Sort | It arranges the table by using a list of one or more column names and optional comparison criteria, specified in the format { { col1, comparison criteria }, { col2 } }. |
| Table.Split | The function divides a single table into multiple tables. The first table in the list includes the first set of rows equal to the specified page size. The following tables in the list contain subseque... |
| Table.SplitAt | The function returns a list consisting of two tables: one table that includes the first N rows of the original table (determined by count) and another table containing the remaining rows. By enumerati... |
| Table.SplitColumn | Divides the designated columns into multiple new columns using the designated splitting function. |
| Table.StopFolding | Prevents any subsequent processes from altering the original data source in the table. |
| Table.ToColumns | Generates a hierarchical structure of nested lists based on the contents of the table named "table." Each nested list represents a row of the table, with each item within the list corresponding to a c... |
| Table.ToList | Transforms a table into a list by utilizing a designated combining function on each row of values within the table. |
| Table.ToRecords | Transforms a table into a list of individual records. |
| Table.ToRows | Generates a hierarchical structure of lists derived from the table named "table". Each item in the list represents a row of values within an inner list. |
| Table.TransformColumnNames | The process of modifying column names is achieved by employing the designated nameGenerator function. Users can specify the maximum length of the new column names by using the MaxLength option. If the... |
| Table.TransformColumnTypes | The function retrieves a table from the input table by performing a transformation operation on the columns specified in the parameter typeTransformations (format: {column name, type name}), with the ... |
| Table.TransformColumns | The table is modified by performing the operations specified in transformOperations on each column. The format for transformOperations is { column name, transformation } or { column name, transformati... |
| Table.TransformRows | Generates a list by executing the transform operation on each individual row in a table. |
| Table.Transpose | Produces a new table by transposing the columns and rows of the original input table. |
| Table.Unpivot | The process involves converting a group of columns in a table into pairs of attributes and values, which are then merged with the remaining values in each row. |
| Table.UnpivotOtherColumns | Converts all columns, except for a defined set, into attribute-value pairs, while retaining the original values in each row. |
| Table.View | The Table.View function allows for the customization of the behavior of operations applied to a view of a table by specifying handler functions. If a table is provided, the handler functions are optio... |
| Table.ViewError | Generates a customized error log based on the errorRecord, ensuring it will not activate a fallback response when thrown by a handler specified on a view (via Table.View). |
| Table.ViewFunction | Generates a custom view function by utilizing a function that can be managed within a view established by Table.View. The OnInvoke handler within Table.View serves to establish a handler for the view ... |
| Tables.GetRelationships | Determines the connections between a group of tables that have a structure resembling a navigation table. The dataColumn specifies the column that holds the data within the tables. |