cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate I
Advocate I

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

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: 

700000000081 123456/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 :
700000000081123456/21/2020

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

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

 

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

flow.jpg

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. 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Update : 

 

So I have Parse Json step in :

pars.JPG

 

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)

flow1.png

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

variable.JPG

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"

 

table1.jpg

View solution in original post

3 REPLIES 3
Advocate I
Advocate I

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?

 

 

Update : 

 

So I have Parse Json step in :

pars.JPG

 

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)

flow1.png

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

variable.JPG

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"

 

table1.jpg

View solution in original post

Hello,

 

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

 

 

Helpful resources

Announcements
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (42,354)