Power Automate flow runs slowly (apply to each)

[Anonymous]

Hi all! I'm pretty new into PowerAutomate but I have to say: I love it! But right now I'm desperate to find help.

So let me explain what I want the flow to do. I have quite a few pdf-files saved in SharePoint (approx. 2000). All of those files just have their file-names available as info. Furthermore, I got an Excel table that not only has the file-name but also several more information regarding each file. I now want to map the respective file with the respective data in Excel and update/ complement the pdf file properties in SharePoint with said data. I dont know if its of any importance to say that I'm using lists in SharePoint. Despite, I hope you can understand what I'm trying to accomplish. 
As the title says my flow runs very slowly. It took over 9h to completely updating approx. 100 files (or even less). The slow run time definitely is because of my "Apply to Each" action. I think 9h for 100 files is way too long, or am I wrong? 
Maybe some of you does know how I can fix this issue in my flow, or even some having different approaches that will work faster. Im open for every help I can get! Thank you very much! I will add some pictures so you can visualize how my flow works right now.
Thank you!

ABMHR

[Matthy79]

Hello @Anonymous 

I guess it will be possible to optimize the flow a lot. But first of all please explain some steps to me.

1.) What is the first apply to each for? You have all file properties in the output of the first step and now you do a "Get file properties" for each file, put it in a Key Value Pair Array without a key and after that into a compose. So after that apply to each you have the last file information in the compose action. I don't understand this part.

2.) Why do you need the variables?

3.) It is not only an apply to each, but it is an apply to each inside an apply to each. This is a nightmare for Power Automate.

4.) Inside this second apply to each you again get the properties of a specific file. Don't you have this information from the first step of the flow? You could use filter array to optimize it a lot, but I'm pretty sure there is a better way to setup this flow. Let's try to optimize it step by step after I understand everything better.

[Anonymous]

Hi @Matthy79 
thank you for your answer! I'll try to answer you question to the best of my ability. As I said I'm new to PA and pretty much doesn't have any knowledge. 
1.) with the first apply to each (select filename only) I tried to get rid of all unnecessary properties of the pdf file. So that I only have the FilenameWithExtension as an output. I reckoned this would help to optimize a bit because the flow would have to need to look through all properties of the pdf for the filename. I left the key blank, because I don't know the effect of it. I still get my desired output  the FilenameWithExtension for each file. I guess, the Compose is fairly unnecessarly. I hope this helps a bit to understand.

2.) The XLFileName variable is unnecessary. But the other two I think I have to have because Excel formats its Date as a numeral so I then later (in Update File properties) have following expression to set the date as DD/MM/YYYY. This is the expression I use (I adjust the varDate accordingly): 

addDays('1899-12-30',int(variables('varDate1')),'yyyy-MM-dd') 
Do you happen to know if there's a work around?

3.) Yes, PA did it automatically. I tried to put the output of the Compose/ Select action from earlier as a value but it didn't work. This way it is know it did work, so i thought it would be okey-ish, until i saw that it needs 9+ hrs for approx 100 files...

4.) Well, thats what i thought, but when I used the Output of the Compose or Select, I got an error/ no true outcome in the condition.

Thank you once again for taking your time and trying to help me. I hope my given info is helping you to understand the flow a bit more. If you need anything else, dont bother to ask 🙂 

[Matthy79]

Hi @Anonymous 

1.) Why do you think the properties are unnecessary? You could use them (for example) to check if the properties should be updated at all. Because if all properties are already set to the values of the excel file, you won't even have to update. Even if you would need this new array. I don't get why you have to use an apply to each for it. All information you need is stored in the first action "Get files (properties only) 2". Select takes an array and gives you an array back. And also if you don't need a key-value pair you could switch to text mode and only set one value. So no apply to each and no "Get file properties" needed.

2.) The formula looks good but you won't need a variable to update it. Instead of "variables('varDate1')" you can also put in the value the dynamic content you first used to set the variable. So you don't have to initialize those variables and because it is inside an apply to each you save a lot of steps.

3.) PA adds an apply to each automatically if you select a value inside an array. We will get to this later.

4.) OK, let's get to this later too.

5.) What if there is an entry in excel and you don't find that file on sharepoint?

6.) What if there is a file on sharepoint but no entry inside of excel?

I will try to summarize everything again and you can tell if I understood it correctly.

There is an excel where somebody fills out detailed information about files stored on Sharepoint. You want to update the file properties of each file on Sharepoint with the information stored in excel. I didn't get the information about the lists you talked about and where they are used.

I think if I got it correct and you answer the last questions I hopefully have all the information to redesign the flow.

2.) 

[Anonymous]

@Matthy79 

1.) Because all the other information, such as "Author", FullPath etc. whatever else is considered as a property is of no use for my instance. Maybe let me try to explain my thought differently.
I have saved in SharePoint invoices (the pdf-files i mentioned). My task originally was to set following additional information per hand (as properties for the files): InvoiceNumber, PostingDate, InvoiceDate (just to name a few) Those other properties/ information are stored in the excel table like this:

FileName	InvoiceNumber	PostingDate	InvoiceDate
Invoice000001.pdf	123456	06.07.2023	30.06.2023

And the only useful way to map/ associate the data in excel with the files in sharepoint is the FileName, because this will be equal (if applicable/ found in both). This is why all other properties are not of use, at least in my mind. 
Regarding your other questions under 1.) why I did it the way I did: I dont know, I guess, it didn't worked like i wanted it and thats why i tried until it did. 

2.) Ah okey, cool! Thank you, I will adjust it.

5.) Maybe for this case an indicator for which entry is missing would be useful. So i can look if I have the file somewhere else stored.
6.) This will most certainly be the case. I had this already and all the properties were left blank, which is completely fine by me. 

Regarding your summary: The excel file is already final - no changes to be made. Otherwise I reckon your summary is correct. 

I'll summarize as well in case we have deviations in the understanding.

I have an Excel file in which data for invoices are stored. The file is final. In SharePoint I have already uploaded the respective invoices. Their file name is everything I got to map each file with the correct row/ information from excel. If the Filename in SharePoint is equal to the filename of a row in excel, I want the complementary information to be added to the properties of the file.
I hope I could make my intend clearer and not cause anymore confusion.

[Matthy79 · Accepted solution]

Hello @Anonymous 

sorry for the late response but I wasn't home most of the day.

Here is what I would do:

From in the last select would be your Excel values.

Now you have an array with all the information you want to update including the Sharepoint ID to do the updates.

Wenn was unklar ist, dann schick mir eine PM 😉