Split delimited text in a cells into rows in a new table following form submission

[MattMannion]

HI all, 

I am trying to automate a process, currently : 

1. A Colleague visits a site and collates a list of issues. 
2. Sends an email with the issues they found. 
3. I collate this into an excel file 
4. Send the total output to one team 
5. Create an itemised list of issues per site for a second team to work through.

This is labour intensive and time consuming as the data I receive is not uniform, So I have create a form using MS forms.  

As MS forms does not allow for multiple answers to one question, the user has to list all issues within one text box.  I can take each row (Submission) and create a power query that splits the submission into multiple rows based on delimiters within a cell and add to a work sheet using the Store number as a title.

Before: 

7000000000

81

12345

6/21/2020

///*no:  1  *Model: 2   *S/N: 3  *Action: 4 ///*no:  1  *Model: 2   *S/N: 3  *Action: 4 ///*no:  1  *Model: 2   *S/N: 3  *Action: 4 ///*no:  1  *Model: 2   *S/N: 3  *Action: 4

After: 

REF :	4 Digit  Store No.:	code :	Date completed :	List of issues resolved :
7000000000	81	12345	6/21/2020	///*no:  1  *Model: 2   *S/N: 3  *Action: 4
7000000000	81	12345	6/21/2020	///*no:  1  *Model: 2   *S/N: 3  *Action: 4
7000000000	81	12345	6/21/2020	///*no:  1  *Model: 2   *S/N: 3  *Action: 4
7000000000	81	12345	6/21/2020	///*no:  1  *Model: 2   *S/N: 3  *Action: 4
7000000000	81	12345	6/21/2020	///*no:  1  *Model: 2   *S/N: 3  *Action: 4

I have successfully automated steps 2,3 and 4 with the flow below : 

But have hit a dead end when automating step 5 (Create an itemised list of issues per submission for a second team to work through).

I have tried to create a flow that mimics exactly what I do manually but I don,t know if this is even the best solution. 

I have successfully created a new worksheet, dynamically using the "4 digit store number" but have hit a dead end trying to add a new table as I do not have a table name to enter as its dynamically selected in the previous step. 

Is there a way to create a separate worksheet per site in excel ? , or should I be looking to create the itemised list in a different program. 

[MattMannion]

Update : 

I have started two separate branches

One to update the core table as shown above and second I have used Parse JSON on the Body of the form response as I figure this will make the splitting easier when I get to that stage. 

The issue I have now is I cannot use the Store number dynamic title for my table, if I use "new" it works fine. I suspect it might be because its a number field rather than text. Could this be the case ? 

Its strange as I can use the same dynamic entry for the work sheet and there is no error. 

Can a worksheet and a table not have the same name?

[MattMannion · Accepted solution]

Update : 

So I have Parse Json step in :

And managed to get a work sheet and table created with a dynamic title (I was trying to use a numerical title for the table)

I believe I have now split the "List all issues that require a part:" answer via the "///" delimiter. 

I now stuck add the second "add a row to a table" as the columns section does not show as the table has only just been created in the flow above. It only shows "ITEM"

[rohsh354]

Hello,

Thanks for sharing the scenario and the solution. I can be very helpful for other users in similar situation.