cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
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

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.

View solution in original post

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!

Microsoft Ignite

Microsoft Power Platform: 2021 Release Wave 1 Plan

Power Platform release plan for the 2021 release wave 1 describes all new features releasing from April through September 2021.

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 (43,135)