Edit

Share via

Table manipulation functions

  • Article

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.