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

Searching for Fields from Spreadsheet

Hi,

 

I've created a flow which does the following;

 

  1. Lists the files in a Folder
  2. For each file;
    1. Convert the document to text
    2. Add a line to a spreadsheet where there is a column for each search term within the text. At present, each column has the formula below which searches for the term and returns Yes if it finds the term in that file
      1. if(contains(string(body('Convert_Word_DOCX_Document_to_Text_(txt)')),'[Search Term, also the column name]'),'Yes','')

 

The issue is, I currently have about 20 search terms and columns in the output spreadsheet, for each one I paste in the above formula and then manually type in the search term. In future this number of terms will increase and I dont want to be manually writing the formula. Is there a way I refer to the output column name in the formula as the search term, or an array or something else that would do this? Obviously the search term needs to match the output column name;

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-litu-msft
Community Support
Community Support

Hi @chrisingham,

 

You could initialize an array variable, then List all rows of the terms Excel table. Then use the Apply to each action to loop through these rows, append each item into the variable:

Annotation 2020-05-27 092450.jpg

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi @chrisingham,

 

After you get all term and put them into an array, you could use the contains condition or contains() function to determine the value from Excel whether exist in the array, for example:

Annotation 2020-06-01 172100.jpg

 

 

If Yes, append these pieces of information into the Rows array variable:

Annotation 2020-06-01 172213.jpg

Then loop the Rows array, input these pieces of information into the Excel table:

Annotation 2020-06-01 172335.jpg

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-litu-msft
Community Support
Community Support

Hi @chrisingham,

 

You could initialize an array variable, then List all rows of the terms Excel table. Then use the Apply to each action to loop through these rows, append each item into the variable:

Annotation 2020-05-27 092450.jpg

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello,

 

Thank you for this suggestion, I see that this would allow me to have an array with each of the search terms listed in the array, however I then need to somehow run the if statements and add more values to the array which I'm not sure how to do. For example, I think the steps would be as follows;

 

  1. List items in the spreadsheet which contains the search functions
  2. Create an array from the search functions
  3. Apply to each document in the folder, then apply to each item in the array, if document contains array item name then add Yes to the array otherwise no,  
  4. Repeat for each document and keep adding rows to the array
  5. Then finally write all data from the array into a spreadsheet

 

Or

 

  1. List items in the spreadsheet which contains the search functions
  2. Create an array from the search functions
  3. Apply to each document in the folder, then apply to each item in the array, if document contains array item name then add Yes to the array otherwise no,  
  4. write the data from the array into a spreadsheet
  5. Reset the array
  6. Repeat for each item

 

I'm not sure how to do the later steps listed above, and I'm not that familiar with arrays, I see them as a matrix of effectively columns and rows which represent values added to the array but I'm not sure if it works like that in flow. 

 

Any suggestions or examples would be great

 

Thanks

Chris

 

 

Hi @chrisingham,

 

After you get all term and put them into an array, you could use the contains condition or contains() function to determine the value from Excel whether exist in the array, for example:

Annotation 2020-06-01 172100.jpg

 

 

If Yes, append these pieces of information into the Rows array variable:

Annotation 2020-06-01 172213.jpg

Then loop the Rows array, input these pieces of information into the Excel table:

Annotation 2020-06-01 172335.jpg

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (3,302)