- 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
Text Functions
The text function enables you to return part of a string, search for text within a string, or concatenate string values. Some additional functions help you to control the formats for dates, times, and numbers.
Following is the list of text functions supported in Wyn Enterprise:
CONCATENATE
Description
The CONCATENATE function joins two text strings into one text string.
Syntax
CONCATENATE(<text1>, <text2>)Parameters
Parameter | Description |
|---|---|
text1, text2 | The text strings to be joined into a single text string. Strings can include text or numbers. You can also use column references. |
Return Value
The concatenated string.
Example
CONCATENATE("Year:",Year('Sale'[Date]))The above example will take the Date column with the year, concatenates it with "Year:", and returns such as "Year:2017"
CONCATENATEX
Description
The CONCATENATEX function concatenates the result of an expression evaluated for each row in a table.
Syntax
CONCATENATEX(<table>, <expression> [,delimiter])Parameters
Parameter | Description |
|---|---|
table | The table containing the rows for which the expression will be evaluated. |
expression | The expression to be evaluated for each row of the table. |
delimiter (optional) | A separator. |
Return Value
A text string.
Example
CONCATENATEX(Addresses, [Country] & " " & [City], ",")In the above example, if the Addresses table is as follows:
Country | City |
|---|---|
China | Xian |
Korea | Seoul |
Then the result will be :
“China Xian， Korea Seoul”
FIND
Description
The FIND function returns the starting position of one text string within another text string. FIND is case-sensitive.
Syntax
FIND(<find_text>, <within_text>)Parameters
Parameter | Description |
|---|---|
find_text | The text you want to find. Use double quotes (empty text) to match the first character in within_text. |
within_text | The text containing the text you want to find. |
Return Value
The number shows the starting point of the text string you want to find.
Example
FIND("Jack", 'Customer'[Name])In the above example, if the Name column of the Customer table is as follows:
Name |
|---|
Tom， Jack |
Jack |
Then it will return the following:
6 |
1 |
LEFT
Description
The LEFT function returns the specified number of characters from the start of a text string.
Syntax
LEFT(<text>, <num_chars>)Parameters
Parameter | Description |
|---|---|
text | The text string containing the characters you want to extract, or a reference to a column that contains the text. |
num_chars | The number of characters you want LEFT to extract. |
Return Value
A text string.
Example
LEFT('Product'[Name], 3)The above example will return the first three characters in the Name column of the Product table.
LEFTTRIM
Description
The LEFTTRIM function removes all spaces from text left except for single spaces between words.
Syntax
LEFTTRIM(<text>)Parameters
Parameter | Description |
|---|---|
text | The text from which you want spaces removed, or a column that contains the text. |
Return Value
The string with spaces removed.
Example
LEFTTRIM('Product'[ColorName])The above example will remove the spaces at the beginning of each row in the ColorName column.
LEN
Description
The LEN function returns the number of characters in a text string.
Syntax
LEN(<text>)Parameters
Parameter | Description |
|---|---|
text | The text whose length you want to find, or a column that contains the text. Spaces count as characters. |
Return Value
A whole number that indicates the number of characters in the text string.
Example
LEN('Product'[Name])The above example will return the number of characters per row in the Name column.
LOWER
Description
The LOWER function converts all letters in a text string to lowercase.
Syntax
LOWER(<text>)Parameters
Parameter | Description |
|---|---|
text | The text you want to convert to lowercase or a reference to a column that contains the text. |
Return Value
Text in lowercase.
Example
LOWER('Product'[Name])The above example will convert the characters of each row in the Name column to lowercase.
MID
Description
The MID function returns a string of characters from the middle of a text string, given a starting position and length.
Syntax
MID(<text>, <start_num>, <num_chars>)Parameters
Parameter | Description |
|---|---|
text | The text string from which you want to extract the characters or a column that contains the text. |
start_num | The position of the first character you want to extract. Positions start at 1. |
num_chars | The number of characters to return. |
Return Value
A string of text of the specified length.
Example
MID("abcde", 2, 1))The above example will return "b".
REPLACE
Description
The REPLACE function replaces part of a text string, with a different text string.
Syntax
REPLACE(<source>, <old_text>, <new_text>)Parameters
Parameter | Description |
|---|---|
source | The text string from which you want to extract the characters, or a column that contains the text. |
old_text | The string of text that contains the characters you want to replace, or a reference to a column that contains the text. |
new_text | The replacement text for the specified characters in old_text. |
Return Value
A text string.
Example
REPLACE('Products'[ProductName],"OB","OC")The above example will replace "OB" with "OC" in the ProductName column.
RIGHT
Description
The RIGHT function returns the last character or characters in a text string, based on the number of characters you specify.
Syntax
RIGHT(<text>, <num_chars>)Parameters
Parameter | Description |
|---|---|
text | The text string that contains the characters you want to extract, or a reference to a column that contains the text. |
num_chars | The number of characters you want RIGHT to extract. You can also use a reference to a column that contains numbers. |
Return Value
A text string containing the specified right-most characters.
Example
RIGHT('Products'[ProductName], 3)The above example will take the last three characters in the ProductName column.
RIGHTTRIM
Description
The RIGHTTRIM function removes all spaces from text right except for single spaces between words.
Syntax
RIGHTTRIM(<text>)Parameters
Parameter | Description |
|---|---|
text | The text from which you want to remove spaces, or the column that contains text. |
Return Value
The string with spaces removed.
Example
RIGHTTRIM('Product'[ColorName])The above example will remove the spaces at the end of each row in the ColorName column.
UPPER
Description
The UPPER function converts a text string to all uppercase letters.
Syntax
UPPER (<text>)Parameters
Parameter | Description |
|---|---|
text | The text you want to be converted to uppercase, or a reference to a column that contains the text. |
Return Value
The same text, in uppercase.
Example
UPPER ('Product'[Name])The above example will convert the contents of the Name column to all uppercase.
TRIM
Description
This function will remove all spaces from the text except for single spaces between words.
Syntax
TRIM (<text>)Parameters
Parameter | Description |
|---|---|
text | The text from which you want to remove the spaces, or a column that contains text. |
Return Value
The string with removed spaces.
Example
TRIM("This column has trailing spaces. ") The above formula will create a new string that will not have trailing white space.
VALUE
Description
This function will convert the text string (that represents a number) to a number.
Syntax
VALUE (<text>)Parameters
Parameter | Description |
|---|---|
text | The text that has to be converted. |
Return Value
Converted numbers with the decimal data type.
Example
VALUE("10") The above formula will convert the typed string, "10", into the numeric value 10.