cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
drichards1
Frequent Visitor

Generating Multiple Rows on a CSV from One Form Response

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:

NamesRolesCourse
Name 1; Name 2; Name 3Role of Name 1; Role of Name 2; Role of Name 3ABCD123

 

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

NameRoleCourse
Name 1Role of Name 1ABCD123
Name 2Role of Name 2ABCD123
Name 3Role of Name 3ABCD123

 

What would be the best way to do so?

Many thanks,

Darren

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-yujincui-msft
Community Support
Community Support

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
  }
]

vyujincuimsft_0-1658996302418.png

Result Screenshot:

vyujincuimsft_1-1658996455063.png

 

 

Best Regards,

Charlie Choi

 

View solution in original post

drichards1
Frequent Visitor

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.

drichards1_1-1660594539695.png

 

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

drichards1_2-1660594702174.png

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.

drichards1_3-1660595142169.png

 

 

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

drichards1_4-1660595218000.png

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. 

drichards1_5-1660595536334.png

drichards1_6-1660595873339.png

 

  • 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"
  }
]

View solution in original post

4 REPLIES 4
drichards1
Frequent Visitor

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
Community Support
Community Support

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
  }
]

vyujincuimsft_0-1658996302418.png

Result Screenshot:

vyujincuimsft_1-1658996455063.png

 

 

Best Regards,

Charlie Choi

 

drichards1
Frequent Visitor

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.

drichards1_1-1660594539695.png

 

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

drichards1_2-1660594702174.png

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.

drichards1_3-1660595142169.png

 

 

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

drichards1_4-1660595218000.png

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. 

drichards1_5-1660595536334.png

drichards1_6-1660595873339.png

 

  • 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"
  }
]

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

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (1,279)