Skip to main content

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 IDDescriptionSyntax
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)