Go to website
Back
Articles on:
Transforms
Data transformation without code
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 tables
Very popular
Average 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 the
Very popular
Change 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 and last names in all normal case "Title Case". We can use the Change text casing step to turn our first and last names into upper case. As seen in our output data below, this step made our "firs
Popular
Clean 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.  with the merged values separated b
Some readers
Pivot 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 repeated "Order Ids" with columns containing "Variant Options" and "Variant Values". We'll want to create a unique row per "Or
Some readers
Remove 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 50 rows with these 3 columns: "Webinar ID", "Registrant name", and "Registration time". After using the Remove duplicate rows step to remove duplicate rows in the "Webinar ID" column, we went from 50 rows to 41. This tell
Some readers
Rename 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 orders data that informs us of the units sold per product variant. We have a column called "ID", but we'd need this to be more exact about what "ID" this actually is for our colleagues. We can use the **Rename
Some readers
Select 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 four columns: "Variant ID", "Product Name", "Variant Name", and "Sale Price". After connecting our data into this step, it produces an output of the two columns selected to keep: "Variant ID" and "Product Name".  you choose. Sort rows Input/output The example data we'll connect to this step has a table of 50 webinar registrants. Let's say we're wanting to view this data with the top being the newest registrants by registration time. We can do this by setting a sorting rule where the va
Some readers
Split 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 single column called "Data". We'll use the Split column step to split this single column into three different columns using the delimiter of a dash mark "-". The output data will look like this:  on top of one other. Stack tables Input/Output The Stack tables step needs at least two data inputs. In the example below, we have two tables that both have the columns "Webinar ID" and "Registrant Name". . 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 w
Some readers