Data Transformations
To clean the retrieved data into your preferred format, you can apply data transformations by defining formulas using Excel-like syntax.
Formulas
You can perform various data transformations using our formulas, such as creating if conditions with =IF()
, replacing characters with =REPLACE()
, and concatenating data with =CONCATENATE()
. Our product offers a wide range of formulas, many of which you might recognize from Microsoft Excel or Google Sheets.
Formula examples
=CONCATENATE(firstName;" "; lastName)
=REPLACE(phoneNumber; 1; 0; "+49")
=IF(OR(amount > 10; amount < 2); amount; 20)
=TEXT(closingDate;"dd.mm.yyyy")
These are just a few examples of the many formulas available in our tool. Using these, you can efficiently perform complex data transformations.
Date & time
Function ID | Description | Syntax |
---|---|---|
DATE | Returns the specified date as the number of full days since today | DATE(Year; Month; Day) |
DATEDIF | Calculates distance between two dates, in provided unit parameter (“D”, “M” or “Y”). | DATEDIF(Date1; Date2; Units) |
DATEVALUE | Parses a date string and returns it as the number of full days since today | DATEVALUE(Datestring) |
DAY | Returns the day of the given date value. | DAY(Number) |
DAYS | Calculates the difference between two date values. | DAYS(Date2; Date1) |
HOUR | Returns hour component of given time. | HOUR(Time) |
ISOWEEKNUM | Returns an ISO week number that corresponds to the week of year. | ISOWEEKNUM(Date) |
MINUTE | Returns minute component of given time. | MINUTE(Time) |
MONTH | Returns the month for the given date value. | MONTH(Number) |
NOW | Returns current date + time as a number of days since today. | NOW() |
TODAY | Returns an integer representing the current date as the number of full days since today. | TODAY() |
Logical
Function ID | Description | Syntax |
---|---|---|
AND | Returns true if all arguments are true. | AND(Logicalvalue1; Logicalvalue2; ...; Logicalvalue30) |
IF | Specifies a logical test to be performed. If the test is true the second argument is returned. If it is false, the third argument is returned. The third argument is optional. | IF(Test; Then value; Otherwisevalue) |
NOT | Complements (inverts) a logical value. | NOT(Logicalvalue) |
SWITCH | Evaluates a list of arguments, consisting of an expression followed by a value. | SWITCH(Expression1; Value1[; Expression2; Value2[...; Expression_n; Value_n]]) |
OR | Returns true if at least one argument is true. | OR(Logicalvalue1; Logicalvalue2; ...; Logicalvalue30) |
XOR | Returns true if an odd number of arguments evaluates to true. | XOR(Logicalvalue1; Logicalvalue2; ...; Logicalvalue30) |
Math & trigonometry
Function ID | Description | Syntax |
---|---|---|
ABS | Returns the absolute value of a number. | ABS(Number) |
CEILING | Rounds a number up to the nearest multiple of Significance. | CEILING(Number; Significance) |
CEILING.PRECISE | Rounds a number up to the nearest multiple of Significance. | CEILING.PRECISE(Number[; Significance]) |
COMBIN | Returns number of combinations (without repetitions). | COMBIN(Number; Number) |
COMBINA | Returns number of combinations (with repetitions). | COMBINA(Number; Number) |
DECIMAL | Converts text with characters from a number system to a positive integer in the base radix given. | DECIMAL("Text"; Radix) |
EVEN | Rounds a positive number up to the next even integer and a negative number down to the next even integer. | EVEN(Number) |
EXP | Returns constant e raised to the power of a number. | EXP(Number) |
FLOOR | Rounds a number down to the nearest multiple of Significance. | FLOOR(Number; Significance) |
INT | Rounds a number down to the nearest integer. | INT(Number) |
MOD | Returns the remainder when one integer is divided by another. | MOD(Dividend; Divisor) |
POWER | Returns a number raised to another number. | POWER(Base; Exponent) |
ROUND | Rounds a number to a certain number of decimal places. | ROUND(Number; Count) |
ROUNDDOWN | Rounds a number down, toward zero, to a certain precision. | ROUNDDOWN(Number; Count) |
ROUNDUP | Rounds a number up, away from zero, to a certain precision. | ROUNDUP(Number; Count) |
SQRT | Returns the positive square root of a number. | SQRT(Number) |
SQRTPI | Returns sqrt of number times pi. | SQRTPI(Number) |
SUBTOTAL | Computes aggregation using function specified by number. | SUBTOTAL(Function; Number1; Number2; ...; Number30) |
SUM | Sums up the values of the specified cells. | SUM(Number1; Number2; ...; Number30) |
Operator
Function ID | Description | Syntax |
---|---|---|
ADD | Adds two values. | ADD(Number; Number) |
CONCAT | Concatenates two strings. | CONCAT(String; String) |
DIVIDE | Divides two values. | DIVIDE(Number; Number) |
EQ | Tests two values for equality. | EQ(Value; Value) |
LTE | Tests two values for less-equal relation. | LEQ(Value; Value) |
LT | Tests two values for less-than relation. | LT(Value; Value) |
GTE | Tests two values for greater-equal relation. | GEQ(Value; Value) |
GT | Tests two values for greater-than relation. | GT(Value; Value) |
MINUS | Subtracts two values. | MINUS(Number; Number) |
MULTIPLY | Multiplies two values. | MULTIPLY(Number; Number) |
UNARY_PERCENT | Applies percent operator. | UNARY_PERCENT(Number) |
Text
Function ID | Description | Syntax |
---|---|---|
CHAR | Converts a number into a character according to the current code table. | CHAR(Number) |
CLEAN | Returns text that has been "cleaned" of line breaks and other non-printable characters. | CLEAN("Text") |
CONCATENATE | Combines several text strings into one string. | CONCATENATE("Text1"; ...; "Text30") |
EXACT | Returns true if both text strings are exactly the same. | EXACT("Text"; "Text") |
LEFT | Extracts a given number of characters from the left side of a text string. | LEFT("Text"; Number) |
LEN | Returns length of a given text. | LEN("Text") |
LOWER | Returns text converted to lowercase. | LOWER("Text") |
MID | Returns substring of a given length starting from Start_position. | MID("Text"; Start_position; Length) |
PROPER | Capitalizes words given text string. | PROPER("Text") |
REPLACE | Replaces substring of a text of a given length that starts at given position. | REPLACE("Text"; Start_position; Length; "New_text") |
REPT | Repeats text a given number of times. | REPT("Text"; Number) |
SPLIT | Divides the provided text using the space character as a separator and returns the substring at the zero-based position specified by the second argument. SPLIT("Lorem ipsum"; 0) -> "Lorem" – SPLIT("Lorem ipsum"; 1) -> "ipsum" | SPLIT("Text"; Index) |
TEXT | Converts a number into text according to a given format. | TEXT(Number; Format) |
TRIM | Strips extra spaces from text. | TRIM("Text") |
UPPER | Returns text converted to uppercase. | UPPER("Text") |
Best Practices
Mathematical Operators
As you can see above, you can use all the mathematical operators. But instead of using ADD()
, MINUS()
, DIVIDE()
and MULTIPLY()
we recommend you to use +
, -
, /
and *
. This saves you time when creating formulas.
For example, you would create a formula for a multiplication like =amount*costs
instead of =MULTIPLY(amount; costs)
.
Delimiter (";" instead of ",")
If you are used to Excel, you probably use ,
as the delimiter for formulas with more than one argument. When you work inside our data pipeline tool, you must adjust because our syntax prefers to use ;
as a delimiter, similar to Google Sheets.
Compare operators
If you want to compare specfic values, you can use the following operators: =
, >
, <
, >=
or <=
.
For example, if you want to check if the amount is greater than 1000, the formula would be: =IF(amount > 1000; Then value; Otherwise value)