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)