Using Array Expander Transform

The Array Expander Transform allows you to take a column of arrays and split each item in the array into its own row of data. This will effectively expand the rows of data you have to be at the granularity of each array item.

Arrays

An Array is a data structure that consists of a list of values (e.g., numbers, strings, etc.). Most frequently, arrays are denoted by a list of values wrapped in a pair of square brackets.

For example:

  • An array of Number values: [1, 2.3, 5, 7.09]

  • An array of String values: [John, Jane, Brad]

  • An array of Date values: [2020-02-01, 2019-03-25, 2010-07-05]

Using the Transform

You can find the Array Expander Transform in the transform list within the builder. In order for the Array Expander Transform to work, the column of arrays that you are using must be wrapped in square brackets.

Screen_Shot_2020-11-03_at_12.53.52_PM.png

Let us say we have a table of data where each row corresponds to an individual and their top three favorite colors.

Screen_Shot_2020-10-22_at_10.59.40_AM.png

If we wanted to make a metric that counts how many times each color was selected, we would need to break out each color into their own row.

Screen_Shot_2020-10-22_at_10.59.50_AM.png

To do this, simply rename the column that you wish to expand in the New Column Name field. Then select the column with the array values you wish to expand from the Column to Expand dropdown list. Hit Run and your data will expand to one row per value in the arrays.

Screen_Shot_2020-11-03_at_12.57.03_PM.png

As you saw in our example, the id and name fields were duplicated each three times, which was the number of values in each rows array field.

When expanding an array you are duplicating the data in all of the other columns by the number of values in the array.

Additionally, any rows that have an empty array, denoted by a pair of brackets with nothing in them (e.g., [] ), can be removed by selecting the Exclude rows with an empty array checkbox. Otherwise they will be kept within your table.

Following images show the output for checked and unchecked Exclude rows with an empty array field, respectively.

Screen_Shot_2021-12-17_at_11.34.05_AM.pngScreen_Shot_2021-12-17_at_11.34.15_AM.png

Considerations

An Array of JSON Objects

If you have an array of JSON objects, you will need to use the Array Expander Transform before you can pull out data from the JSON. Learn more about the JSON Explorer transform.

Was this article helpful?