How to automatically create a new row with data from existing row in new sheet

Question

I need help creating a workflow that will create a new row in a new sheet based on specific information in a row. I'm using Microsoft Forms to capture orders for our customers. The form allows the requester to order multiple line items. When the info is transferred to Excel, it comes over in one row.

I would like to break out each line item into a new row, while maintaining some information like the name, email, department, etc.

Current row shown below with two orders:

Blue Highlight: Product #1 information

Yellow Highlight: Product #2 information

.

What I would like to create from current row in a new sheet:

All info the same except product information

.

Answer 1

Hi,

This M code works

let

Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], 

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"File Upload", "Additional Information:", "Add as an Ariba Watcher to your request?", "Additional Items?", "Deliver To:", "PFP/Internal Order Code:", "Cost Center:", "Requester Business Function / Department", "Priority:", "Email Address:", "Requested By:", "Last modified time", "Name", "Email", "Completion time", "Start time", "ID"}, "Attribute", "Value"), 

#"Inserted Text After Delimiter" = Table.AddColumn(#"Unpivoted Other Columns", "Text After Delimiter", each Text.AfterDelimiter([Attribute], ":"), type text), 

#"Replaced Value" = Table.ReplaceValue(#"Inserted Text After Delimiter","","1",Replacer.ReplaceValue,{"Text After Delimiter"}), 

#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Replaced Value", {{"Attribute", each Text.BeforeDelimiter(\_, ":"), type text}}), 

#"Pivoted Column" = Table.Pivot(#"Extracted Text Before Delimiter", List.Distinct(#"Extracted Text Before Delimiter"[Attribute]), "Attribute", "Value"), 

#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Text After Delimiter"}) 

in

#"Removed Columns"

Hope this helps.

Answer 2

Thanks Cliff, I'm new to Microsoft Forms but I'll see what I can do with these details.

Answer 3

Hello Ashish,

Thank you for your help!

The first line are the column headers.

The second line is how the data is received with two product line items.

• Black Bold = Product #1 info
• Green Bold = Product #2 info

The new sheet would have one row with all columns and product #1 info only

Second row with all columns and product #2 info only

ID	Start time	Completion time	Email	Name	Last modified time	Requested By:	Email Address:	Priority:	Requester Business Function / Department	Cost Center:	PFP/Internal Order Code:	Deliver To:	Type of Order:	Manufacturer:	Manufacturer Part #:	Description:	Quantity:	Unit of Measure:	Price:	Additional Items?	Type of Order:2	Manufacturer:2	Manufacturer Part #:2	Additional part # (if applicable):2	Description:2	Quantity:2	Unit of Measure:2	Price:2	Add as an Ariba Watcher to your request?	Additional Information:	File Upload
1	12/7/23 9:30:17	12/7/23 9:33:39	*	John		John	*	Normal	Accounting	*	*	*	Consumable	*	*	Product #1	1	*	299	YES	Consumable	*	*		test 2	5	*	22	No	n/a

Answer 4

Dear Nas,

Yes, you can do that via Power Automate.

You can create an array to split the row in table1 to two rows in table2.

Here is an example flow for your reference.

Form submission:

.

Value of Initialize variable:

[

{

"Product": "Product1 value",

"Description": "Description1 value",

"Quality": "Quality1 value"

},

{

"Product": "Product2 value",

"Description": "Description2 value",

"Quality": "Quality2 value"

}

]

Compose: items('Apply_to_each')?['Product']

Compose2: items('Apply_to_each')?['Description']

Compose3: items('Apply_to_each')?['Quality']

.

Result:

.

Thanks for your effort and time.

Sincerely

Cliff | Microsoft Community Moderator

Answer 5

Hi,

Share data in a format that can be pasted in an MS Excel file.