- Getting Started
- Administration Guide
-
User Guide
- An Introduction to Wyn Enterprise
- Document Portal for End Users
- Data Governance and Modeling
- Working with Resources
- Working with Reports
- Working with Dashboards
- View and Manage Documents
- Understanding Wyn Analytical Expressions
- Section 508 Compliance
- Subscribe to RSS Feed for Wyn Builds Site
- Developer 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" )The above example will return a table and add a column to the original table, Sales. The added column is named NamePlus, and the content is the content of the Name column of the Product table plus the character "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"))In the above example, the expression first adds a column to the original table Product, the added column is named NamePlus, and the content is the content of the Name column of the Product table plus the character "1".
The duplicate rows in the table are then deduplicated, and finally, a table containing only distinct rows is returned.
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]))The above example returns a table with a single row and column named "total" and the content is the total result of the Amount column in the Sales table.
Remarks
The parameters must always appear in pairs of name and expression.
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])In the above example, with the Product table as the base table, the Name column and the SaleID column are returned.
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])If for the above example, the Sales table is:
OrderId | Amount | ProductKey |
|---|---|---|
1 | 10 | 101 |
2 | 17 | 102 |
3 | 9 | 103 |
The Product table is:
ProductID | Name |
|---|---|
101 | Fruit |
102 | Egg |
103 | food |
104 | juice |
Then the above example will return:
AmountPlusOne | Name |
|---|---|
18 | Egg |
10 | food |
11 | Fruit |
Remarks
SELECTCOLUMNS differ from ADDCOLUMNS in a way that SELECTCOLUMNS does not start with the specified < Table >, but starts with an empty table and then adds columns.
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]))If in the above example, if the Sales table is :
OrderID | Amount | ProductKey |
|---|---|---|
1 | 10 | 101 |
2 | 17 | 102 |
3 | 9 | 103 |
4 | 12 | 101 |
If the Product table is:
ProductID | Name |
|---|---|
101 | Apple |
102 | Egg |
103 | Rice |
104 | juice |
The above example will return:
Name | Tota |
|---|---|
Apple | 22 |
Egg | 17 |
Remarks
Each column that defines the name must have a corresponding expression; Otherwise, an error is returned. The first parameter, name, defines the name of the column in the result. The second parameter expression defines the calculation that is performed to get the value of each row in the column.
groupBy_columnName must be in the table or the table related to the table.
Each name must be enclosed in double-quotes.
The function groups a selected set of rows into a set of summary rows based on the values of one or more groupBy_columnName columns. Each group returns one row.
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]))If in the above example, the Sales table is :
OrderID | Amount | ProductKey |
|---|---|---|
1 | 10 | 101 |
2 | 17 | 102 |
3 | 9 | 103 |
4 | 12 | 101 |
If the Product table is:
ProductID | Name | CategoryKey |
|---|---|---|
101 | Apple | 1 |
102 | Egg | 2 |
103 | Rice | 2 |
104 | juice | 3 |
The Category table is:
categoryID | CategoryName |
|---|---|
1 | Fruit |
2 | Food |
3 | Drinking |
4 | Sea food |
The above example will return:
Name | Total |
|---|---|
Apple | 22 |
Remarks
The function does not guarantee any sort order of the results.
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)The above example will return the top 100 products sold in descending order by CategoryName.
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)The above example will skip the first 15 rows, then take the top 100 product data and sort them.
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)))For the above example, if the Product table is :
Name |
|---|
a |
b |
The above example will return:
Name |
|---|
a |
b |
a1 |
b1 |
Remarks
Both tables must have the same number of columns.
Columns are grouped by position in their respective tables.
The column names in the returned table will match the column names in table_expression1.
Keep duplicate rows.
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])The above example will return a single list of deduplicated contents of the Name column in the Product table.
FILTERS
Description
FILTERS function, will return the values that are directly applied as filters to then ColumnName.
Syntax
FILTERS(ColumnName)Parameters
Parameter | Description |
|---|---|
ColumnName | Existing columnname, using standard WAX expression syntax. It cannot be an expression |
Return Value
The values that are directly applied as filters to columnname.
Example
COUNTROWS(FILTERS(FactInternetSales[ProductKey])) The above example determines the number of direct filters the column [ProductKey] has.