The table manipulation functions return a table or manipulate existing tables.
Following is the list of table manipulation functions supported in Wyn Enterprise:
Description
The ADDCOLUMNS function adds calculated columns to the given table or table expression.
Syntax
ADDCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>...]
Parameters
Return Value
A table with all its original columns and the added ones.
Example
ADDCOLUMNS('Sales', "NamePlus",'Product'[Name] & "1" )
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.
DISTINCT(<table>)
For column, a column of unique values. For table, a table containing only distinct rows.
DISTINCT(ADDCOLUMNS('Product',"nameplus",'Product'[Name] & "1"))
The ROW function returns a table with a single row containing values that result from the expressions given to each column.
ROW(<name>, <expression>[[,<name>, <expression>]...])
A single row table.
ROW("total",SUMX('Sales','Sales'[Amount]))
The SELECTATTRIBUTES function returns a table with selected columns from the table. The first parameter table is the base table.
SELECTATTRIBUTES(<tableName> | <columnName> [,[columnName] [,...]])
Returns a table with the same number of rows as the table or column specified.
SELECTATTRIBUTES('Product'[Name],'Sales'[SaleID])
The SELECTCOLUMNS function adds calculated columns to the given table or table expression.
SELECTCOLUMNS(<table>, <name>, <expression> [, <name>, <expression>]...)
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]) © GrapeCity, Inc. All Rights Reserved. 1.800.858.2739 Why Wyn Product Blog Videos Samples
SELECTCOLUMNS('Sales',"AmountPlusOne",'Sales'[Amount] + 1, "Name",'Product'[Name])
The SUMMARIZE function returns a summary table for the requested totals over a set of groups.
SUMMARIZE (<table>, <groupBy_columnName>[, <groupBy_columnName>]...[, <name>, <expression>]...)
A table with the selected columns for the groupBy_columnName arguments and the summarized columns designed by the name arguments.
SUMMARIZE (Filter('Sales','Sales'[Amount] > 10),'Product'[Name],"total",SUMX('Sales','Sales'[Amount]))
The SUMMARIZECOLUMNS function returns a summary table over a set of groups.
SUMMARIZECOLUMNS(<groupBy_columnName> [, <groupBy_columnName>]..., [<filterTable>]...[, <name>, <expression>]...)
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.
SUMMARIZECOLUMNS( 'Product'[Name],Filter('Category','CategoryID'=1),"total",sumx('Sales','Sales'[Amount]))
The TOPN function returns the top N rows of the specified table.
TOPN(<n_value>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]...])
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.
TOPN(100, SUMMARIZE('Product','Product'[name],'Product'[CategoryName],"TOTAL",SUM('Sales'[Amount])),'Product'[name], 'Product'[CategoryName],DESC)
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.
TOPNSKIP(<rows>,<skip>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]...])
An entire table or a table with one or more columns.
TOPNSKIP(100, 15, SUMMARIZE('Product','Product'[name],'Product'[CategoryName],"TOTAL",SUM('Sales'[Amount])),'Product'[name], ASC, 'Product'[CategoryName], DESC)
The UNION function creates a union (join) table from a pair of tables.
UNION(<table_expression1>, <table_expression2> [,<table_expression>]...)
A table that contains all the rows from each of the two table expressions.
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)))
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.
VALUES(tableNameOrcolumnName)
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.
VALUES('Product'[Name])