Manipulate Dataset to Extract Values from String and Use as Unique Identifiers
Looking for some guidance about how to fully accomplish this data manipulation use case. In the original data set, you'll see that the emails are embedded within a string (so there could be many emails listed for a single value in column 1). Example listed in Original:
Looking for a way to extract individual emails and put them into separate rows with their corresponding values in the next row. See Requested:
I've built a sample with batch processing, extract the row values corresponding to column 1 and then converting to text > making comma delimited > then converting back to a table with their corresponding value in column 1 included. I've gotten to the point that I can get the emails into their own rows with their corresponding column 1 values but then if the email has multiple column 1 values, I'm struggling to combine those together with commas into a single cell. The final version of what I've gotten in my process is below. As you can see, email1 appears in two rows but needs to be collapsed into one with 123,456
in column 2. Running into issues specifically around the generic column names (Column 1) when I try to batch again to consolidate the codes for each email.
Example process exported and attached.
So looking for guidance on two things:
1. Is there an easier way to accomplish this process build without batching and the table > text > table steps?
2. If this is an efficient way to build this process, how would you recommend combining the column 2 values to be collapsed for each unique email address?
Answers
-
@Meghan_904209 I do expect that using Tables: Start workflow for each row will be the easiest way to accomplish this:
1. Use Tables: Create a table for each unique value to define your table to batch over and use the filtered tables in step 2.
2. Use Tables: Convert data table to text to get your comma delimited list.
3. Use Tables: Update a row to add your comma delimited list back into the original table or a copy.0