Table manipulation functions
These functions return a table or manipulate existing tables.
In this category
| Function | Description |
|---|---|
| ADDCOLUMNS | Adds calculated columns to the given table or table expression. |
| ADDMISSINGITEMS | Adds combinations of items from multiple columns to a table if they do not already exist. |
| CROSSJOIN | Returns a table that contains the Cartesian product of all rows from all tables in the arguments. |
| CURRENTGROUP | Returns a set of rows from the table argument of a GROUPBY expression. |
| DATATABLE | Provides a mechanism for declaring an inline set of data values. |
| DETAILROWS | Evaluates a Detail Rows Expression defined for a measure and returns the data. |
| DISTINCT column | Returns a one-column table that contains the distinct values from the specified column. |
| DISTINCT table | Returns a table by removing duplicate rows from another table or expression. |
| EXCEPT | Returns the rows of one table which do not appear in another table. |
| FILTERS | Returns a table of values directly applied as filters to columnName. |
| GENERATE | Returns a table with the Cartesian product between each row in table1 and the table that results from evaluating table2 in the context of the current row from table1. |
| GENERATEALL | Returns a table with the Cartesian product between each row in table1 and the table that results from evaluating table2 in the context of the current row from table1. |
| GENERATESERIES | Returns a single column table containing the values of an arithmetic series. |
| GROUPBY | Similar to the SUMMARIZE function, GROUPBY does not do an implicit CALCULATE for any extension columns that it adds. |
| IGNORE | Modifies SUMMARIZECOLUMNS by omitting specific expressions from the BLANK/NULL evaluation. |
| INTERSECT | Returns the row intersection of two tables, retaining duplicates. |
| NATURALINNERJOIN | Performs an inner join of a table with another table. |
| NATURALLEFTOUTERJOIN | Performs a join of the LeftTable with the RightTable. |
| ROLLUP | Modifies the behavior of SUMMARIZE by adding rollup rows to the result on columns defined by the groupBy_columnName parameter. |
| ROLLUPADDISSUBTOTAL | Modifies the behavior of SUMMARIZECOLUMNS by adding rollup/subtotal rows to the result based on the groupBy_columnName columns. |
| ROLLUPISSUBTOTAL | Pairs rollup groups with the column added by ROLLUPADDISSUBTOTAL within an ADDMISSINGITEMS expression. |
| ROLLUPGROUP | Modifies the behavior of SUMMARIZE and SUMMARIZECOLUMNS by adding rollup rows to the result on columns defined by the the groupBy_columnName parameter. |
| ROW | Returns a table with a single row containing values that result from the expressions given to each column. |
| SELECTCOLUMNS | Adds calculated columns to the given table or table expression. |
| SUBSTITUTEWITHINDEX | Returns a table which represents a left semijoin of the two tables supplied as arguments. |
| SUMMARIZE | Returns a summary table for the requested totals over a set of groups. |
| SUMMARIZECOLUMNS | Returns a summary table over a set of groups. |
| Table Constructor | Returns a table of one or more columns. |
| TOPN | Returns the top N rows of the specified table. |
| TREATAS | Applies the result of a table expression as filters to columns from an unrelated table. |
| UNION | Creates a union (join) table from a pair of tables. |
| VALUES | Returns a one-column table that contains the distinct values from the specified table or column. |