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

(0) Share

Report

Posted on by MattMannion

HI all,

I am trying to automate a process, currently :

A Colleague visits a site and collates a list of issues.
Sends an email with the issues they found.
I collate this into an excel file
Send the total output to one team
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

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.

All responses (4)

Answers (1)

Suggested answer

takolota1 4,750 on at

Like (0)

Report

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

There is a way to split a column on delimiters to multiple records using only 6 actions & it will work for any number of records.
https://community.powerplatform.com/galleries/gallery-posts/?postid=b4b53bc0-0e9a-ef11-8a69-6045bdee0f9b
Rohit Sharma354 on at

Like (0)

Report

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

Hello,

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

MattMannion 67 on at

Like (0)

Report

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

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"
MattMannion 67 on at

Like (0)

Report

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

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?