After you have converted your strings to arrays, if a sufficient percentage of input strings from a column are successfully converted to the other data type, the column may be retyped. 

Output: Returns the Array data type value for strInput String values.

str_input

Literal, column name, or function returning String values that are to be evaluated for conversion to Array values.

• Missing values for this function in the source data result in null values in the output.
• Multiple columns and wildcards are not supported.

Example - parsing strings as an array

Source:

The following table represents raw imported CSV data:

In the above table, you can see that the two items columns are interpreted differently. In the following steps, you can see how you can parse the data as array values before producing a new column intersecting the two arrays.

Transformation:

Create a new column to store the array version of itemsA:

Remove the quotes from the column:

Now create the array by merging the array text value with square brackets and then using the PARSEARRAY function to evaluate the merged value as an array:

You can create the second array column using a similar construction in a new column:

Since both columns have been parsed as array values, you can use the ARRAYINTERSECT function to find the common values:

Results: