Power Platform Community Forum Thread Details

Hi -

I have following output from a form in Excel (data copied from the form into a new Excel Workbook in Sharepoint).

ID	Name	Main Cat	Sub Cat
1	abc	Food	Des;IC
2	def	Food;Fruit	Apple;Orange

Is there a way to create a flow in Power Automate do to something like this:

ID	Name	Main Cat	Sub Cat
1	abc	Food	Des
1	abc	Food	IC
2	def	Food	Apple
2	def	Food	Orange
2	def	Fruit	Apple
2	def	Fruit	Orange

Any thoughts?

Thanks - Jerry

Categories:

Power Apps Pro Dev & ISV

Yes you can. You would need to do the following

1. Have your Trigger, either manual or possibly triggered when a file arrives in SharePoint or OneDrive

2. Make sure that the Original Excel Sheet Data has an Excel Table around it. If it does NOT, you will need to add one manually or using the Create Table (excel action)

3. Do a List rows in a Table (excel action) to pull all the rows from the workbook/sheet that you are treating as the Source

4. Add a Condition

in the left add the expression length() in the expression window

Then without closing the window, click the Dynamic tab, click your mouse between the ( and ), scroll down until you find the Dynamic Property Body/Value from step 3 and click on it to insert it betweeh the ( )

in the middle put is greater than

in the right type 0

This way it checks if you found any rows in the excel file.

5. in the Yes side of the condition

Add an Apply to each , to loop through all the rows, and rename it to be called TableRow (you rename by clicking the ... and the end of the header and choosing Rename

NOTE: Before you can create rows in your target sheet, you need to make sure that your target sheet exists and that the Excel Table for the Data is already configured. If not again you need to use Create Table or manually create the sheet and add the table.

Inside the apply to each, we need to add another Apply to each, which we will call LineDetail (again rename it)

The reason for THIS apply to each is because we want to loop through each Row in the Excel, and then we need to Split the string in your Sub Cat column, and then loop through all the strings and create rows in your new sheet.

In the Second apply to each you will do a Add Row to a Table in excel.

now this part is important, because you need data from both Loops to fill it in, so your Apply to each LineDetail its Input would be

split(items('TableRow')?['Sub Cat'], ',')

This will make the inside loop for every string such as your example data of

Des;IC

In the Add Row to a Table (Excel) we will get the data from both loops

you will access the ID, Name and Main Cat from the TableRow loop so all of them will be typed as items('TableRow')['ID'] or ['Name'] or ['Sub Cat']

NOTE you are NOT or'ing them together, its just the replacement value to be ID or Name or Sub Cat

And for your Sub Cat value you can just type currentItem or Click the Dynamic Property currentItem from the Properties window under your LineDetail loop

Now you will generate a single line for each Sub Cat that you entered that is separated by a , (comma)