Creating Reports

[BenETMCAT]

I am trying to pull data from either multiple excel workbooks or multiple sheets within the same workbook. The data needs to be matched based on a variable value in the spreadsheets. For instance, on one sheet I have a customer name and a machine serial number, plus a lot of other info that I need to be able to look through. On the other sheet it is the same way just without the customer name. I need to pull all of the data from each sheet pertaining to a specific customer based on the serial numbers and group them together. Then I need to take the data for each customer and fill out a word template. After filling out the word template, the document then needs to be saved and a notification sent to me that it is ready for review. Is there any way to do this? It seems like the answer is right there, but I can't seem to find it. The main problem is that the excel files will be changing each month with new customers or removed customers, same with all the other info. There is also nearly 1300 customers to go through, which is why I was hoping to use Power Automate to do a lot of the repetitive work for me. This will have to be done monthly.

[Andrew_J]

@BenETMCAT 

I think there is a way, but I will ask you about the end of your question first.  Is the new customer data coming in with new records only and is it coming in from a single source only?

I may propose we handle this in a different way when I get your responce.

Regards,

Andrew

[BenETMCAT]

@Andrew_J 

Basically, I will download the excel files each month. They come from a proprietary website. The files would also include data from the previous month as well, but it is possible that some of the customers will no longer be on the list as the whole thing is based on a contract basis. They are optional contracts that are able to be purchased by the customer at any point. They also have expiry dates. All the info would be coming from an excel file. As I said, it could be from a single file with multiple sheets or from separate files. Whatever is easiest.

[Andrew_J]

@BenETMCAT 

Okay thanks for the responce.

I allways like SharePoint Lists, it was what I started off with in M365, so I have stuck with them.  I am therefore thinking that you should go down that route.  But this then means:

• We will have to have flow to import the data each month check for duplicates
• We shall need to use lookups in one or many lists based on certian common data
• deal with the Word Template structure
• deal with the approvals

If the lists are going to something only you will use then that is okay but if others are using them you may need to train them.

You will need a premium flow licence to use the WORD TEMPLATE feature, but I have had some expericne with that so know the basics.  I assume you have this licence. 

Let me know is this something you see as being useful or would you prefer me to stop now and let someone else take over.  If you do decide to go ahead then I operate in finding information and then working with you on a stage by stage basis.  But there is a limit to my time and it would be good to know when you need it by?

Regards,

Andrew

[BenETMCAT]

@Andrew_J 

Definitely sounds useful. I'm going to talk to one of the system administrators tomorrow who is familiar with the power platform. If he cannot help me then I might enlist your help. I was hoping there might've been a quick fix. I will let you know if I need your help or not by Monday at the latest. Thanks for the info so far though!

[Andrew_J]

@BenETMCAT 

Understood. 

Andrew

[BenETMCAT · Accepted solution]

Ended up working this out. First, I took the main excel data and performed a Power Query through a new spreadsheet to get only the relevant data I need and sort it all into tables. In order to update the data each month I simply download new source files and set them in the queries. Within that Power Query, I created a new query which sorted the data based on customer name and performed the various formulas that I needed for the rest of the info that had to be extrapolated. After preparing that document, I used the Excel connector in Power Automate to list the table rows in my Customer Name table. Then performed an apply to each loop that contained five functions. The first function was another list rows function that listed the rows in my utilization table. Then I took that data through the Select function in order to turn it into a map. After that, I used the Compose function to create an array with that data. Then I took all of the necessary data from the apply to each loop and the composed array and plugged it into the Word template function inside the loop. This fills out the template for each customer. After that I used the create file function on the Sharepoint connector to post all of the filled templates to a Sharepoint folder. Finally, outside of the loop, I used the Outlook connector to send me an email when the flow is complete. This did what I wanted.