Turn form responses into individually named transposed excel files

[AndrewMac]

I am trying to create a flow that takes individual forms responses as shown and creates an individually named excel file containing a tranposed version of the responses

Candidate Number	First Name	Last Name	1	2	3	4	5
12345	John	Doe	A	B	A	C	D

into this format (an excel file called '12345_First Name_Last Name) for subsequent import into SQL DB

Candidate Number	12345
First Name	John
Last Name	Doe
1	A
2	B
3	A
4	C
5	D

Can anyone help/advise please?

[Koen5 · Accepted solution]

Dear AndrewMac,

I see 3 options to achieve what you want, but before that, I am wondering what you eventually want to do with the data. It seems to me that the structured data (table) you get from the form is perfectly suitable for any kind of analysis you need. Your solution may lie in changing the way you use the data.

Anyways:

1.  Change the original form to produce the output data as you want. This may be the easiest way, but this depends on if you have the rights to change the form output.
2. Take the output from the form, get it as pure text and use various compose actions in which you change the text and syntax to match the 'transposed' structure you require ... after this you load the text as an array.
3. Use Power Automate Desktop to use the transpose action from excel in excel.

Hope the suggestions help you further along.

Happy flowing,

Koen

[AndrewMac]

Hi Koen

Thank you for your detailed response.  I don't have much experience of Forms or Power Automate to be honest but I think I grasp the concept.  Sadly I cannot change my form format, it has to be this way.  I do have admin rights and would happily listen to any suggestions you may have with a view to maybe altering the way I do this task.

Thanks

Andy