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

Building Power Automate flow

Hi, 

 

I am beginner, and trying to build a flow in power automate to update the column in my SharePoint folder against the files (With extension) matching Names (Without extension) in an excel sheet in SharePoint folder with corresponding comments in the excel sheet.  

 

Here is the snapshot of my flow, i am getting error as Update file properties not accepting the ID.

 

My flow may be wrong, as I need to input dynamic ID which matches the file name and update corresponding comments.

 

Please help suggest to achieve the results...

 

My Excel looks like:

 

Screenshot123.png

 

My Flow looks like:

 

Screenshot2.png

Screenshot3.png

Thanks,

Sudhanva.

 

 

 

 

 

3 ACCEPTED SOLUTIONS

Accepted Solutions

That looks right though it might just be a lot to process and you might have to break it out into batches. Is there any way to reduce the amount of files it needs to process? Maybe by the date the file was last modified? 

View solution in original post

Sudhanva6982
Helper I
Helper I

Yes i think we can batch them based on the date file is created, i observed aprox 500 files created yearly and expecting it will increase going forward, also we can batch the data in excel in to different tables and use them.

 

could you please suggest what is the best way? and how we can use as batches in the loops to avoid timeout? Appreciate your help here...

View solution in original post

@creativeopinion thanks a lot for your help and appreciate your guidelines for beginners, i finally achived it and found that the main cause for "File with ID missing in the sharepoint folder" is due to many running flows from long time in my "Run History", which it seems causing missing file error.

 

Secondly as suggested by you I batch them update only current year files by using the filter query expression in Get file properties: 

Created ge '2023-01-01T00:00:00Z'

 

Below screen shot of the final flow, now it is running in just 25 seconds. thanks for the help.

 

Sudhanva6982_0-1691443397207.png

 

View solution in original post

28 REPLIES 28
creativeopinion
Super User
Super User

There are two ways to approach this:

 

If you are using a Filter Array on the Get files (properties only) action, I would recommend pulling that action outside of the Apply to Each loop. Otherwise you'll be unnecessarily running that action each time you loop through a row in your spreadsheet. 

 

Run the action once before the Apply to Each loop and filter the results.

 

creativeopinion_0-1690658777159.png

To return the ID, you will need to use an expression.

int(body('Filter_array')?[0]['ID'])


Alternatively, you can remove the Filter Array action and use a Filter Query in the Get files (properties only) action. This option will only work if you have an exact match (eg. eq). 

 

For the file name, you need to use this Filter Query:

FileLeafRef eq '[Candidate].extension'

 

creativeopinion_2-1690659439731.png

To return the ID number you will need to use an expression:

outputs('Get_files_(properties_only)')?['body/value']?[0]['ID']

 

If I helped you solve your problem—please mark my post as a solution .
Consider giving me a 👍 if you liked my response!

👉 Watch my tutorials on YouTube
👉 Tips and Tricks on TikTok

 

Hi,

Thanks for your kind information and appreciate your time here...

 

I tried both solutions suggested by you, 

 

Soln 1:

Getting error as ID is not int type as expected in "update file properties". The main issue i am struggling is i need to update Comments column in the sharepoint dir for matching name files: kindly refer below screenshots, kindly suggest...

 

Sudhanva6982_0-1690746679019.png

 

Sudhanva6982_2-1690746764710.pngSudhanva6982_4-1690746943778.png

 

 

 

Soln 2:

 

The expression you have mentioned i used as is, do i need to mention the exact extension as .pdf or .dpc? because, the sharepoint dir i am working with has .pdf, .doc .txt and .docx files.

 

I tried below approach, flow ran successfully but condition results false even though there are matching names, i suspect the expression " FileLeafRef eq '[Candidate].extension' " is not working as expected, please suggest.

 

Sudhanva6982_5-1690748375447.png

 

 

Sudhanva6982_6-1690748460582.png

 

Test Result:

 

 

Sudhanva6982_8-1690748591896.png

 

 

Matching file in folder & Excel: here file extensions are different (PDF, DDOC, DOCX, eml and TXT)

 

Sudhanva6982_9-1690748671384.png

Sudhanva6982_10-1690748993395.png

 

 

 

Thanks for your help....

 

 

 

 

 

 

 

 

 

This part of your flow is incorrect, you need to insert an expression, what you have here is just a string of text. 

creativeopinion_0-1690751445353.png

 

In the Compose action you need to click on the Expression tab. 

creativeopinion_1-1690751524238.png

 

The output of the expression should be an ID number.

 

Yes i added as an expression this time, but still no luck...

Sudhanva6982_1-1690752940248.png

 

 

Sudhanva6982_2-1690752974946.png

 

 

Can you upload a screenshot of your Filter Array action? 

 

It doesn't seem to be returning any results. 

here is the filter array screen shot:

 

Sudhanva6982_0-1690759036307.png

 

Here i highlighted expression of the compose as well

 

Sudhanva6982_1-1690759425695.png

 

The Compose - File ID expression is not working in your instance because in my flow I was filtering out a single item from the Get Items action. However, you are using a Filter Array action.

 

Your flow has to be adjusted to account for that fact that you are returning multiple items instead of one. 

 

Before you can even tackle that you need to troubleshoot why your Filter Array action isn't returning any items. 

 

In order to troubleshoot. I would recommend adding a Compose action above the Filter Array action to store the Candidate value.

 

Remove the Compose - File ID action all together. You'll need to take a different approach anyway. 

 

The Filter Array action, is checking to see if the Name values from the the Get Files (properties only) action contains the Candidate value.

 

Run a test to see the Filter Array action is returning any results for any of your Excel rows. If not, the Filter Array action needs to be adjusted.

Ok after checking what i understood is when we select output (for filter array) from "List row present in a table" (Excel) for the apply to each loop, ID filter array will not return any thing, how ever if i nest this loop in another apply to each loop with "Get file (properties only)"(Sharepoint folder), then compose will get ID, but it wont work for comparing excel column when we select Candidate column to compare.

 

So i changed my approach, now my below approach is working fine, but the loop is getting timed out, may be i am adding too much nested loop, or might be a bad approach, could you please suggest??

 

New approach:

I am working on the Doc files first, docx next and then pdf, so i have 2 more same loops for docx and pdf.

 

Sudhanva6982_0-1690912773423.png

 

 

Your approach above will be inefficient when you have a lot of files to loop through. By using an apply to each loop and a condition to check for the files that match each candidate name in your spreadsheet you are looping through all your files to check for files that match. 

 

Instead you need to use the Filter Array action to return the appropriate information to Filter out the files so that you can then update the filtered files properties. 

 

Can you please run these actions in your flow again?

 

creativeopinion_1-1690921551246.png

I would also like you to add an additional compose action above the Filter Array action to hold the Candidate name. 

 

Upload a screenshot of the Compose Action Output and Filter Array action.

Here are the screen shots: now i found that the files are 

 

Failed (Candidate not matching sharepoint Name loop no 21):

 

Sudhanva6982_0-1690929526897.png

Sudhanva6982_1-1690929593266.png

Passed (Candidate matching Sharepoint Name loop no 20): this row successfully updated in share point.

Sudhanva6982_2-1690929734042.pngSudhanva6982_3-1690929774761.pngSudhanva6982_4-1690929804240.png

 

 

Why is the candidate on loop 21 not matching? Are there any files that match? Is there an extra space in the excel cell that has the candidate name?

The excel has list of latest candidate names and the share point has all candidate's files from past few years, so many old file names may not match the excel candidate name, there is no extra space in the excel cell.

 

I just observed that filter array body is with all sharepoint files data when the loop is at no match candidate name, if it matches any candidate name, it will have only matching file properties data in the body, is this the right behavior?

 

Just want to uppdate you, it is updating all matching files with correct comments though, but it is throughing the error for non matching files.

 

 

Okay perfect. You just need to add a condition after the Filter Array action. Check for the length returned from the Filter Array action with an expression.

length([filter array output here])

If the length is equal to 0 (means no matching files returned), do nothing. 

 

Move the Compose ID and Update Files Properties actions to the NO branch.

 

creativeopinion_0-1690934316854.png

 

Awesome yes this ran successfully....!! let me apply this to larger data to my actual folders and let you know the results...

 

Sudhanva6982_0-1690935556505.png

 

After adding large data (823 X 10 rows/column table in excel, and 1300 files in share point), the flow is timed out at 10minutes as below... 

 

Should we process it in batches?

 

Sudhanva6982_0-1690936983405.png

 

Have you tried turning on the concurrency control in the Apply to Each action?

Yes i turned on the concurrency control and set the degree of parallelism to 50, but still it is timing out at 9 or 10 min

 

Sudhanva6982_0-1690938244491.png

 

What if you added a Select action to your flow after the List Rows Present in a table action. Use this to only select the values from your table that you need. For example, if you only need the candidate name it would look like this:

creativeopinion_2-1690939681468.png

 

If you need a few other columns, then you can map a key to a column like this:

creativeopinion_1-1690939632946.png

Hope this helps!


Then in the Apply to each action, you would loop through the Select action results instead of the Values from the List Rows present in a table action.

creativeopinion_4-1690939807609.png

 

Hope this helps.

Helpful resources

Announcements

Celebrating the May Super User of the Month: Laurens Martens

  @LaurensM  is an exceptional contributor to the Power Platform Community. Super Users like Laurens inspire others through their example, encouragement, and active participation. We are excited to celebrated Laurens as our Super User of the Month for May 2024.   Consistent Engagement:  He consistently engages with the community by answering forum questions, sharing insights, and providing solutions. Laurens dedication helps other users find answers and overcome challenges.   Community Expertise: As a Super User, Laurens plays a crucial role in maintaining a knowledge sharing environment. Always ensuring a positive experience for everyone.   Leadership: He shares valuable insights on community growth, engagement, and future trends. Their contributions help shape the Power Platform Community.   Congratulations, Laurens Martens, for your outstanding work! Keep inspiring others and making a difference in the community!   Keep up the fantastic work!        

Check out the Copilot Studio Cookbook today!

We are excited to announce our new Copilot Cookbook Gallery in the Copilot Studio Community. We can't wait for you to share your expertise and your experience!    Join us for an amazing opportunity where you'll be one of the first to contribute to the Copilot Cookbook—your ultimate guide to mastering Microsoft Copilot. Whether you're seeking inspiration or grappling with a challenge while crafting apps, you probably already know that Copilot Cookbook is your reliable assistant, offering a wealth of tips and tricks at your fingertips--and we want you to add your expertise. What can you "cook" up?   Click this link to get started: https://aka.ms/CS_Copilot_Cookbook_Gallery   Don't miss out on this exclusive opportunity to be one of the first in the Community to share your app creation journey with Copilot. We'll be announcing a Cookbook Challenge very soon and want to make sure you one of the first "cooks" in the kitchen.   Don't miss your moment--start submitting in the Copilot Cookbook Gallery today!     Thank you,  Engagement Team

Announcing Power Apps Copilot Cookbook Gallery

We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps.   Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Check Out the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community.  We can't wait to see what you "cook" up!    

Welcome to the Power Automate Community

You are now a part of a fast-growing vibrant group of peers and industry experts who are here to network, share knowledge, and even have a little fun.   Now that you are a member, you can enjoy the following resources:   Welcome to the Community   News & Announcements: The is your place to get all the latest news around community events and announcements. This is where we share with the community what is going on and how to participate.  Be sure to subscribe to this board and not miss an announcement.   Get Help with Power Automate Forums: If you're looking for support with any part of Power Automate, our forums are the place to go. From General Power Automate forums to Using Connectors, Building Flows and Using Flows.  You will find thousands of technical professionals, and Super Users with years of experience who are ready and eager to answer your questions. You now have the ability to post, reply and give "kudos" on the Power Automate community forums. Make sure you conduct a quick search before creating a new post because your question may have already been asked and answered. Galleries: The galleries are full of content and can assist you with information on creating a flow in our Webinars and Video Gallery, and the ability to share the flows you have created in the Power Automate Cookbook.  Stay connected with the Community Connections & How-To Videos from the Microsoft Community Team. Check out the awesome content being shared there today.   Power Automate Community Blog: Over the years, more than 700 Power Automate Community Blog articles have been written and published by our thriving community. Our community members have learned some excellent tips and have keen insights on the future of process automation. In the Power Automate Community Blog, you can read the latest Power Automate-related posts from our community blog authors around the world. Let us know if you'd like to become an author and contribute your own writing — everything Power Automate-related is welcome.   Community Support: Check out and learn more about Using the Community for tips & tricks. Let us know in the Community Feedback  board if you have any questions or comments about your community experience. Again, we are so excited to welcome you to the Microsoft Power Automate community family. Whether you are brand new to the world of process automation or you are a seasoned Power Automate veteran - our goal is to shape the community to be your 'go to' for support, networking, education, inspiration and encouragement as we enjoy this adventure together.     Power Automate Community Team

Hear what's next for the Power Up Program

Hear from Principal Program Manager, Dimpi Gandhi, to discover the latest enhancements to the Microsoft #PowerUpProgram, including a new accelerated video-based curriculum crafted with the expertise of Microsoft MVPs, Rory Neary and Charlie Phipps-Bennett. If you’d like to hear what’s coming next, click the link below to sign up today! https://aka.ms/PowerUp  

Tuesday Tip | How to Report Spam in Our Community

It's time for another TUESDAY TIPS, your weekly connection with the most insightful tips and tricks that empower both newcomers and veterans in the Power Platform Community! Every Tuesday, we bring you a curated selection of the finest advice, distilled from the resources and tools in the Community. Whether you’re a seasoned member or just getting started, Tuesday Tips are the perfect compass guiding you across the dynamic landscape of the Power Platform Community.   As our community family expands each week, we revisit our essential tools, tips, and tricks to ensure you’re well-versed in the community’s pulse. Keep an eye on the News & Announcements for your weekly Tuesday Tips—you never know what you may learn!   Today's Tip: How to Report Spam in Our Community We strive to maintain a professional and helpful community, and part of that effort involves keeping our platform free of spam. If you encounter a post that you believe is spam, please follow these steps to report it: Locate the Post: Find the post in question within the community.Kebab Menu: Click on the "Kebab" menu | 3 Dots, on the top right of the post.Report Inappropriate Content: Select "Report Inappropriate Content" from the menu.Submit Report: Fill out any necessary details on the form and submit your report.   Our community team will review the report and take appropriate action to ensure our community remains a valuable resource for everyone.   Thank you for helping us keep the community clean and useful!

Users online (6,036)