Generating Multiple Rows on a CSV from One Form Response

[drichards1]

Hello,

I'm looking to build a flow and have been struggling with which approach to take.

Our users will enter data into a form . For example, I will collect data as follows-- each line is a new question.

Q1: Name 1

Q2: Role of Name 1

Q3: Name 2

Q4: Role of Name 2

Q5: Name 3

Q6: Role of Name 3

Q7: Course

The form is first sent to Sharepoint, where similar values are being grouped together and stored with a separator. In Sharepoint Lists, it looks like:

Names	Roles	Course
Name 1; Name 2; Name 3	Role of Name 1; Role of Name 2; Role of Name 3	ABCD123

With that, I'm hoping to generate a CSV that looks like the following:

Name	Role	Course
Name 1	Role of Name 1	ABCD123
Name 2	Role of Name 2	ABCD123
Name 3	Role of Name 3	ABCD123

What would be the best way to do so?

Many thanks,

Darren

[drichards1]

I thought I would update the community. I was able to figure this out. It took some time, and I'm not sure I can even articulate the solution, but if anyone would like some info-- I can attempt to explain it!

[v-yujincui-msft · Accepted solution]

Hi @drichards1 ,

Glad to see you figured it out.

You could post your solution and mark it as a 'Solution' so that it will be useful for others and close the case.

In addition, I also did a simple test for your reference.

[
  {
    "Name": Q1,
    "Role": Q2,
    "Course": Q7
  },
  {
    "Name": Q3,
    "Role": Q4,
    "Course": Q7
  },
  {
    "Name": Q5,
    "Role": Q6,
    "Course": Q7
  }
]

Result Screenshot:

Best Regards,

Charlie Choi

[drichards1 · Accepted solution]

Hi Charlie,

Thanks for giving this a shot.

I'm attempting to write out my solution. Please know that I am not a Power Automate expert-- so at times I may not be using the correct lingo. Many of these steps I've found from various other threads and have cobbled them together to suit my needs. I truly hope that this is helpful for others. It took a while for me to figure out. 

Step 1: I've created the following variables:

• varBbAccessUsers (string): this stores a semicolon-separated string of usernames which are taken from my form. Example: uname1;uname2;uname3
• varBbAccess Roles (string): this stores a semicolon-separated string of roles which are taken from my form. Example: role1;role2;role3
• varInt (integer): this serves as a "counter" which will generate an ID number to help us later in the process.
• bbUsernameCourseArray (array): This will combine the usernames from above with the course code that was submitted one time in the form, and an index number (from varInt)
• bbRolesCourseArray (array): This will combine the submitted roles from above, along with the course code and an index number.
• bbCombinedArray (array): This will combine the above two arrays into something we can use for our data purposes.

Step 2: Convert each string into array by using Compose. I used the split command to do this, one command for each variable. This step is important, because it turns it from a plain string that has semicolons, into something that can be used for array purposes.

Step 3: Build an array of Usernames, Course Codes, and an Index Number.

I created an "Apply to Each":

• for the Output I used the output of the "Convert usernames to array" compose item.
• I added an "Append to array variable" task to begin applying to the bbUsernameCourseArray variable I had created. The value is set to bring in the Username, Course ID, and apply a unique Index number to it each time.
• At the end, I have an increment variable item which will ensure that the Index is incrementing by 1 each time.

Step 4:  After the Apply to Each, I reset the index variable so that we can use it on another array.

Step 5: Build the next array-- this time, I would like it to include the roles that were submitted for the course.

So, within the apply to each, I've added an "Append to array variable" action:

• Output is set to the output of the "Convert Roles to Array" compose action from Step 2.
• Variable Name is set to the empty bbRolesCourseArray that I created in Step 1.
• Value is similar to Step 3. I continue to apply an index number, and course ID, but this time I am adding the Course Role to the array.

And of course after appending, I increment my index variable by 1 as well.

Step 6: Combine the two arrays. This is the step where we combine all the data and get the final formatting that we are looking for. 

• Created another Apply to Each, and the Output is set to the bbUsernameCourseArray variable that we have now fully populated.
• Add a "Filter Array" item. The purpose of this is to pull up the details for each Index Number between both arrays. This is why adding the Index Number was so important.
  • From: variable bbRolesCourseArray
  • Terms: item()?['Index'] is equal to items('Apply_to_each_3')?['Index']
    • The above essentially ensures that the index of the first array is equal to the index of the second array. This ensures that we are pairing the right data together.
• Add a Compose item - this builds the output for what will become our combined data. The contents of this are pasted below.
  • {
    "Index": @{items('Apply_to_each_3')?['Index']},
    "Course ID": @{items('Apply_to_each_3')?['Course ID']},
    "Username": @{items('Apply_to_each_3')?['Username']},
    "Course Role": @{first(body('Match_index_numbers_between_roles_and_usernames'))?['Course Role']}
    }
• Finally, still within the Apply to Each, add an "Append to Array" item.
  • Name: I'm applying this to another blank variable called bbCombinedArray. 
  • Value: The contents of the above compose item.

Our final data looks like:

[

  {

    "Index": "1",

    "Course ID": "1234.123456",

    "Username": "uname1",

    "Course Role": "Course Builder"

  },

  {

    "Index": "2",

    "Course ID": "1234.123456",

    "Username": "uname2",

    "Course Role": "Instructor"

  }

]

[drichards1]

Hi Charlie,

Also- I wanted to thank you for your time  in drafting this solution. Yours is much simpler- because I am not very familiar with arrays, I did not know you could compose it in the way that you did.

In my case, I am instead pulling from a Sharepoint list value which I was feeding my webforms into (i.e. so Q1, Q2, Q3 as an example are fed into one comma-separated column)... so I think that the solution I did is the only way to achieve it once it is already in the list. Your method is perfect when pulling directly from the form-- and I will use this in future projects.

Thanks again,
Darren