Wyn Enterprise User Guide

Table Manipulation Functions

The table manipulation functions return a table or manipulate existing tables.

Following is the list of table manipulation functions supported in Wyn Enterprise:

ADDCOLUMNS


Description

The ADDCOLUMNS function adds calculated columns to the given table or table expression.

Syntax

ADDCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>...]

Parameters

Parameter Description
table Any analytical expression that returns a table of data.
name The name given to the column, enclosed in double-quotes.
expression Any analytical expression that returns a scalar expression, evaluated for each row of the table.

Return Value

A table with all its original columns and the added ones.

Example

ADDCOLUMNS('Sales', "NamePlus",'Product'[Name] & "1" )

DISTINCT


Description

For column, the DISTINCT function returns a one-column table that contains the distinct values from the specified column. In other words, duplicate values are removed and unique values are returned. For table, the DISTINCT function returns a table by removing duplicate rows from another table or expression.

Syntax

DISTINCT(<table>)

Parameters

Parameter Description
table The table from which unique rows are to be returned. The table can also be an expression that results in a table.

Return Value

For column, a column of unique values. For table, a table containing only distinct rows.

Example

DISTINCT(ADDCOLUMNS('Product',"nameplus",'Product'[Name] & "1"))

ROW


Description

The ROW function returns a table with a single row containing values that result from the expressions given to each column.

Syntax

ROW(<name>, <expression>[[,<name>, <expression>]...])

Parameters

Parameter Description
name The name given to the column, enclosed in double-quotes.
expression Any analytical expression that returns a single scalar value.

Return Value

A single row table.

Example

ROW("total",SUMX('Sales','Sales'[Amount]))

SELECTATTRIBUTES


Description

The SELECTATTRIBUTES function returns a table with selected columns from the table. The first parameter table is the base table.

Syntax

SELECTATTRIBUTES(<tableName> | <columnName> [,[columnName] [,...]])

Parameters

Parameter Description
tableName or columnName A column from which values are to be returned, or a table which is the base table.

Return Value

Returns a table with the same number of rows as the table or column specified.

Example

SELECTATTRIBUTES('Product'[Name],'Sales'[SaleID])

SELECTCOLUMNS


Description

The SELECTCOLUMNS function adds calculated columns to the given table or table expression.

Syntax

SELECTCOLUMNS(<table>, <name>, <expression> [, <name>, <expression>]...)

Parameters

Parameter Description
table Any expression that returns a table.
name The name given to the column, enclosed in double-quotes.
expression Any expression that returns a scalar value like a column reference, integer, or string value.

Return Value

A table with the same number of rows as the table specified as the first argument. The returned table has one column for each pair of , arguments, and each expression is evaluated in the context of a row from the specified

argument.

Example

SELECTCOLUMNS('Sales',"AmountPlusOne",'Sales'[Amount] + 1, "Name",'Product'[Name])

SUMMARIZE


Description

The SUMMARIZE function returns a summary table for the requested totals over a set of groups.

Syntax

SUMMARIZE (<table>, <groupBy_columnName>[, <groupBy_columnName>]...[, <name>, <expression>]...)

Parameters

Parameter Description
table Any expression that returns a table of data.
groupBy_columnName (optional) The qualified name of an existing column is used to create summary groups based on the values found in it. This parameter cannot be an expression.
name The name given to a total or summarize column, enclosed in double-quotes.
expression Any expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row/context).

Return Value

A table with the selected columns for the groupBy_columnName arguments and the summarized columns designed by the name arguments.

Example

SUMMARIZE (Filter('Sales','Sales'[Amount] > 10),'Product'[Name],"total",SUMX('Sales','Sales'[Amount]))

SUMMARIZECOLUMNS


Description

The SUMMARIZECOLUMNS function returns a summary table over a set of groups.

Syntax

SUMMARIZECOLUMNS(<groupBy_columnName> [, <groupBy_columnName>]..., [<filterTable>]...[, <name>, <expression>]...)

Parameters

Parameter Description
groupBy_columnName A fully qualified column reference (Table[Column]) to a base table for which the distinct values are included in the returned table. Each groupBy_columnName column is cross-joined (different tables) or auto-existed (same table) with the subsequent specified columns.
filterTable A table expression that is added to the filter context of all columns specified as groupBy_columnName argument. The values present in the filter table are used to filter before cross-join/auto-exist is performed.
name A string representing the column name to use for the subsequent expression specified.
expression Any expression that returns a single value (not a table).

Return Value

A table that includes combinations of values from the supplied columns based on the grouping specified. Only rows for which at least one of the supplied expressions return a non-blank value are included in the table returned. If all expressions evaluate to BLANK/NULL for a row, that row is not included in the table returned.

Example

SUMMARIZECOLUMNS( 'Product'[Name],Filter('Category','CategoryID'=1),"total",sumx('Sales','Sales'[Amount]))

TOPN


Description

The TOPN function returns the top N rows of the specified table.

Syntax

TOPN(<n_value>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]...])

Parameters

Parameter Description
n_value The number of rows to return. It is an expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row/context).
table Any expression that returns a table of data from where to extract the top 'n' rows.
orderBy_expression Any expression where the result value is used to sort the table and is evaluated for each row of the table.
order (optional) A value that specifies how to sort orderBy_expression values, ascending or descending.

Return Value

A table with the top N rows of the table or an empty table if n_value is 0 (zero) or less. Rows are not necessarily sorted in any particular order.

Example

TOPN(100, SUMMARIZE('Product','Product'[name],'Product'[CategoryName],"TOTAL",SUM('Sales'[Amount])),'Product'[name], 'Product'[CategoryName],DESC)

TOPNSKIP


Description

The TOPNSKIP function retrieves several rows from a table efficiently, skipping several rows. Compared to TOPN, the TOPNSKIP function is less flexible but much faster.

Syntax

TOPNSKIP(<rows>,<skip>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]...])

Parameters

Parameter Description
rows Number of rows to return.
skip Number of rows to skip.
table Table expression made by physical columns of the data model that are not grouping the cardinality of the original table they belong to.
orderBy_expression Expression used to define the sort order of the result.
order A value that specifies how to sort orderBy_expression values, ascending or descending.

Return Value

An entire table or a table with one or more columns.

Example

TOPNSKIP(100, 15, SUMMARIZE('Product','Product'[name],'Product'[CategoryName],"TOTAL",SUM('Sales'[Amount])),'Product'[name], ASC, 'Product'[CategoryName], DESC)

UNION


Description

The UNION function creates a union (join) table from a pair of tables.

Syntax

UNION(<table_expression1>, <table_expression2> [,<table_expression>]...)

Parameters

Parameter Description
table_expression Any analytical expression that returns a table.

Return Value

A table that contains all the rows from each of the two table expressions.

Example

UNION(SUMMARIZE(SELECTCOLUMNS('Product',"NAME",'Product'[name]),[NAME],"TOTAL",SUM('Sales'[Amount])),SUMMARIZE(SELECTCOLUMNS('Product',"NAME",'Product'[name] & "1"),[NAME],"TOTAL",SUMX('Sales','Sales'[Amount] + 1)))

VALUES


Description

The VALUES function when the input parameter is a column name, returns a one-column table that contains the distinct values from the specified column. Duplicate values are removed and unique values are returned. A BLANK value can be added. When the input parameter is a table name, returns the rows from the specified table. Duplicate rows are preserved. A BLANK row can be added.

Syntax

VALUES(tableNameOrcolumnName)

Parameters

Parameter Description
tableName or columnName A column from which unique values are to be returned, or a table from which rows are to be returned.

Return Value

When the input parameter is a column name, a single-column table. When the input parameter is a table name, a table of the same columns is returned.

Example

VALUES('Product'[Name])