Power Platform Community Forum Thread Details

Hi All,

Help is highly appreciated.

I have form collecting comma separated values in one of the column like below.

I want lookup excel file (please see attached sample) with splitted values & create worksheets splitting each in new excel file in power automate

Thanks in advance

test data.xlsx

Categories:

Using flows

Hi @CU16041500-0,

To achieve this, you will need to format your lookup data in a table. Power Automate can only access Excel values if they are formatted as table rows. To do that, select all of your data, click on "Format as Table," and choose "My table has headers.":

Once you format it as a table, you have to save the Excel file in either OneDrive or a SharePoint document library. Now you can set up the Power Automate flow. Here is how you can set it up:

Use the "When a form submits" trigger.
Add a "Initialize variable" action. Give the variable any name, data type should be Array, and for the value, write an expression like this: split(triggerOutputs()?['text_1'], ','). This will split your comma-separated values and convert them into an array.
Add an "Apply to each" action and set the variable you just initialized as the items.
Add a "Get a row" action from the Excel Online (Business) connector. Choose the OneDrive or SharePoint location where your lookup matrix is and choose the Table name (if you did not rename the table, it would most likely be called Table1). For Key Column, you have to choose Code, and for Key Value, add a dynamic value like this: items('Apply_to_each')
From here, you can access the product name and the latest date for each comma-separated value and also perform further operations like creating a new file.

Let me know if you need further assistance.

Please ✅ Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item.

If the content was useful in other ways, please consider answering Yes to Was this reply helpful? or give it a Like! 🩷