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.

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (1)

MattMannion 67 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Verified answer

MattMannion 67 on at

Like (0)

Report
Copy link

Link copied!

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"

Was this reply helpful? Yes No
Rohit Sharma354 Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hello,

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

Was this reply helpful? Yes No
Suggested answer

takolota1 4,978 Moderator on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No