Introduction to Transformation
Things to consider First record is header, then use column names to specify the target columns First record is not header, then use the column indexes to specify the target columns Data Transformation Average by group Change text casing Clean data Combine columns Combine tablesSome readersAverage by group
The Average by group step calculates the average of all values in one or more columns. This step is similar to the AVERAGEIF function in Excel. Average by group Input/output Our input data has number of sales orders showing how many quantities of a particular product was sold at a particular date. After connecting data to this step and setting it up, it gives us theSome readersChange text casing
The Change text casing step converts the text in any column to a selected case type. Change text casing Input/output Our input data is customer information that displays first name, last name and company in all normal case "Title Case". We can use the Change text casing step to turn our "First Name", "Last Name" and "Company" columns into upper case. As seen in our outFew readersClean data
The Clean data step removes leading or trailing spaces and other unwanted characters (letters, numbers, or punctuation) from any rows of data you'd like. Clean data Input/output Our input data has a column of "Text" with various leading trailing spaces and punctuations. By using the Clean data step, we can easily remove spaces to clean up the "Text" column. (https://storFew readersCombine columns
The Combine columns step combines data from two or more columns into a single column separated by a specified character. Combine columns Input/output Let's say we need to combine the data from our columns "Product" and "Color" to create a new column called "Combined Orders" for easy label creation. The Combine columns step is how we'll achieve this. We will use "Product" and "Color" columns from the input data. (https://storage.crisp.chat/users/helpdesk/website/2d0674738fbSome readersCombine tables
The Combine Tables step joins multiple tables into one by matching rows between those tables. It is similar to a VLOOKUP in Excel or Google Sheets. In SQL, it's like a join clause. The principle is simple: if we have two tables of data that are related to each other, we can use the Combine Tables step to join them into one table by matching rows between those tables. This step can handle combining two tables at a time. Once we set it up, we can use it repeatedly. Even if the row amount cSome readersCompare dates
The Compare dates step compares dates in a column to another column of dates, or compares dates in a column against the current time when your Easyflow flow runs. This step is similar to the DATEDIF function in Excel by creating a new column showing the measurement of time between two dates. Compare dates Input/output The data you connect to this step needs at least one column with date information. In this example, we pass a column called "OrderDate" and compare that date againstFew readersConvert array of objects to array of arrays
The Convert array of objects to array of arrays converts the array of JSON into an array of arrays. Convert array of objects to array of arrays Input/output Our example input data is a list of contacts. We can use the Convert array of objects to array of arrays step to convert the array of JSON into an array of arrays. The values from each Object will in an array. (https://storage.crispFew readersCount by group
The Count by group step counts the number of rows that exist for each unique value in one or more columns. This step is similar to the COUNTIF function in Excel. Count by group Input/output Our input data is a list of Sales. We want to know how many sales we have available at different product. After connecting the data to this step and setting it up, it gives us the outputFew readersExtract text from column
The Extract text from column step extracts a portion of text based on a matching character or offset. You may use this to pull out company names from emails, remove part of an ID, or extract the timezone from any date/time stamp. Extract text from column Input/output In this case, we're looking to extract email domains from our customer email addresses. Our input data has five columns: "First Name", "Last Name", "Company", "Email" and "Country". (https://storage.crisp.chat/userFew readersFill in blanks
The Fill in blanks step uses the nearest value above, below, to the left, or to the right of a cell in a column. You can also use a custom value or reference another column's value to fill in the blank cell. Fill in blanks Input/output We'll use the below input table for this step to process. It contains four columns including an empty one called "Gender". After using the FiFew readersFilter rows
The Filter rows step keeps rows that satisfy a set of rules. You can use this step to create simple filters with one rule, or complex filters with many rules. Filter rows Input/output Let's say we want to filter the "Product Name" column by setting a rule to include rows where the value is contains the word "Road". We'll use the "Product Name" column from the input data. AfterSome readersFind and replace
The Find and replace step works like you might imagine the name means: You can type in a value, specify the columns to look for it in, and type in a new value to replace it with if it is found. Find and replace Input/output Our input data below is a List of Sales orders. Let's say we need to replace the word Silver in the "ProductName" column with the word YELLOW.Few readersFind maximum by group
The Find maximum by group step calculates the maximum value in a column or a group of columns. This step is similar to the MAX function in Excel. Find maximum by group Input/Output Our input data is showing up the "Product" and its "Quantity". After connecting the data to this step and setting it up, it shows the maximum value in "Quantity" column for each "Product".Few readersFind min/max per row
The Find min/max per row step calculates the minimum or maximum value across a range of columns. Find min/max per row Input/output Our input data shows The Products sales orders including "Order Quantity", Unit Price" and "Freight". After we connect our data to this step, it produces an output with a new column to the right-side called "Min Max Number". The values displFew readersFind minimum by group
The Find minimum by group step calculates the minimum value in a column or a group of columns. This step is similar to the MIN function in Excel. Find minimum by group Input/output Our input data is product names and its information. After connecting it into this step, our output data is a minimum quantity of each product. (https://storage.crisp.chat/users/helpdeskFew readersFlatten rows
The Flatten rows recursively extracts values out of the object into a flattened dictionary. Flatten rows Input/output Our example input data is a list of contacts. We can use the Flatten rows step to Make the child object name "Country" with the same level of the other fields, and its name will be merged with its parent object "MoreInfoCountry" with underscore separator. (https://storFew readersFormat dates
The Find minimum by group step calculates the minimum value in a column or a group of columns. This step is similar to the MIN function in Excel. Format dates Input/output Input data to be connected to this step needs at least one column with date data. In this example, we pass a column called "Order Date" that it currently formatted as MM/DD/YYYY. By requesting the New FormatFew readersFormat numbers
The Format numbers step can reformat numbers across as many columns as you choose in this step's settings. It can add or remove commas, currencies, and accounting formats to numbers, or round numbers and format them as percentages. Format numbers Input/output Let's use some of our store's sales information as the input data. In this example, we'll want to reformat the numbersFew readersInsert date & time column
The Insert date & time column step adds a column with the current date and time, with customizable offset by days and timezones. The current date and time will be determined at the time the flow is run. This step can be useful for tasks like logging updated times. Insert date & time column Input/output Connect any data into this step. It'll give you the output data of a new "Current DateTime" column appended to your dataset where the current date and time is noted (based on when theFew readersInsert growth rate column
The Insert growth rate column step calculates the growth rate from the previous row within a column of your choosing. Insert growth rate column Input/output The input data is a list of products. After connecting input data to this step, it gives us the output of a newly added column called "Growth Rate" showing how much more or less of a product growth compared to tFew readersInsert if/else column
The Insert if/else column step enables you to insert a conditional column filter. A conditional statement is a core building block of any programming language. The goal of each Insert if/else column step is to create one column where the value of each cell in that column is dictated by the rules you create within the step. Insert if/else column Input/output We will use the "Stock" column from the following input data. (https://storage.crisp.chat/users/helpdesk/website/2d0Few readersInsert if/else blank column
The Insert if/else blank column step enables you to insert a conditional column filter for blank values. Insert if/else blank column Input/output The data we will use for this step consists of 4 columns "First Name", "Last Name", "Gender" and "Email". New column name "IF ELSE" will be added into the table, It will check the "Gender" column. If the column cell is not blank the "OK" value will bFew readersInsert math column
The Insert math column step executes a math equation on each row of data. This step functions similarly to writing a formula in a new Excel column. Insert math column Input/output Our input data is a list of sales orders, we will use the "SalesAmount" by "TaxAmt" columns from it. After connecting the data into this step, it produces the output of a new column "MultipFew readersInsert row numbers
The Insert row number step creates a new column adding sequential numbers to each row. You can also add row number based on each set of rows having the same column data, each set of rows will have its own range of sequential numbers. Insert row numbers Input/output The input data we'll use with this step is a table contains "ProductName" column. The new output table hasFew readersInsert rows
The Insert rows step adds any number of rows to a table. You can include default values for each column to make it easier to build a large table with duplicated values. Insert rows Input/output This table has 5 rows with the columns, "First Name", "Last Name", "Company", "Email", and "Country". After using the Insert rows, a new row added to the table. (https://storage.cFew readersInsert running total column
The Insert running total column step calculates the running total sum of any column you choose. Insert running total column Input/output The input data we'll connect to this step is a list of Product orders. The step will give us output data with a new column called "Running Total" that sums the "Quantity Sold" column as it moves down the rows. (https://storage.crisp.chat/users/helpdesk/wFew readersInsert text column
The Insert text column step adds a column filled with repeating row values. The rows can be filled with any integer/number, letter, special character, or value of your choosing. You can also reference other column values and combine them into this new column. Insert text column Input/output Our input data example has two list of products. After using this step, our outputFew readersLimit rows
The Limit rows step filters data by limiting the row amount. You can keep or remove a certain number of rows from the top or bottom at whichever position in a table that you choose. Limit rows Input/output In our input data below, we have the list of product orders. By using the Limit rows step, we can easily filter the number of rows to get the first 5 rows, as seen in our beloFew readersMerge duplicate rows
The Merge duplicate rows step allows you to group rows in one or more columns and merge their values. Merge duplicate rows Input/output The data we'll input into this step has an list of products and sizes. After applying the process, Each group of rows has the same value in the "Size" column, will be merged in a single row separated by a delimiter. (https://storage.cFew readersPivot columns
The Pivot columns step takes a column and turns its values into multiple columns. It is particularly useful when needing to turn order line item information into a unique row per order. Pivot columns Input/Output In the example below, our input data has "Product", "Color", "Quantity" and "Stock" columns. The "Product" values will becomes the column headers and the "Color" valueFew readersRemove duplicate rows
The Remove duplicate rows step removes rows with a duplicate value in any column you choose. Remove duplicate rows Input/output Our input data has a List of Products. After using the Remove duplicate rows and specifying "Product" column to identify the duplicates, The distincted data will displayin the table. (https://storage.crisp.chat/users/helpdesk/website/2dFew readersRename columns
The Rename columns step offers the ability to rename any and all columns that you'd like to create new titles for. Rename columns Input/output Our example input data has a list of sales orders. We can use the Rename columns step to rename the "Order Quantity" column to "Quantity". (https://storage.crisp.chat/users/helpdesk/website/2d0674738fbd8000/rename-columns-outputevjFew readersSelect columns
The Select columns step keeps or removes whichever columns you choose from your dataset. Select columns Input/output In our input data below, there are five columns: "Product", "Color", "Size", "Quantity" and "Total". After connecting our data into this step, it produces an output of the two columns selected to keep: "Variant ID" and "Product Name". (https://storage.crisp.Few readersReorder columns
The Reorder columns step enables you to reorder any of your existing columns.. Reorder columns Input/output In this example, the input data we'll use is a list of sales orders. We can use the Reorder columns step to move our columns into whichever form that makes the most sense to us. As seen in the below screenshot, we've reordered 4 columns out of 8 in the following sFew readersSort rows
The Sort rows step sorts the entire table in ascending or descending order based on values in the column(s) you choose. Sort rows Input/output The example data we'll connect to this step has a table of Sales orders. Let's say we're wanting to view this data by "SalesOrderNumber" column in an Asc order, the data will be shown as the following. (https://storage.crisp.chat/users/Few readersSplit column
The Split column step splits a column into one or more based on a specified delimiter, such as a comma. Split column Input/output Our input data below is a a list of sales orders. We'll use the Split column step to split the "OrderDate" column into three different columns using the forward slash delimiter "/". The output data will look like this: (https://storage.crisFew readersStack tables
The Stack tables step provides the ability to vertically stack any table(s) on top of one other. Stack tables Input/Output The Stack tables step needs at two data inputs. In the example below, we have two tables. After using the Stack tables step, these two tables are now staFew readersSum by Group
The Sum by group step calculates the sum of all values in one or more columns. This functionality is similar to the SUMIF function in Excel. Sum by Group Input/output Our input data is a list of sales orders. After we connect this data to the step, it'll produce an output of that sum the "Sales Amount" and "Tax Amt" columns for for each "Product Name". (https://storage.criFew readersUnpivot columns
The Unpivot columns step display the data based on each value from the identified column(s) with its correspondent row cells and column headers. Unpivot columns Input/output The input data we'll use for this step is a table of colors and quantities for products. Below is a screenshot of our output data after using this Unpivot columns step. The Identified column name is "Few readersUse regex
The Use regex step matches patterns using Regular Expression to find or replace values. Regular Expressions, or RegEx for short, are useful for matching patterns in text or numbers (or anything, really). RegExr.com is an excellent resource to use when composing your regular expression. Before you jump in... We recommend exploring the "Extract text from column," "Find and replace," and "Clean data" steps. These steps are often able to accomplish the same result without wFew readers