Hi @Hywel
Is this something you want automated or do you expect someone to be viewing a screen when its working? Is this something you would wanted automated and also possibly scheduled or triggered?
Yes, you can do all of that pretty easily. Ok I say that but yeah its possible, and I would use Power Automate, not a Power App.
However, if you wanted to use a Power App, you would really need to combine Power App + Power Automate imho.
Summary
User has a list of text items (company names) that they want to modify and then check to see if the names appear on one or more of three Excel Master files containing long lists of company names. They then want a report showing what was found and where.
Ok so it sounds like here you want the person to input the things to correct. So this could be the front end Powe App consuming it.
Then it would call a Power Automate to do the parsing to give the canvas app back the Report. OR if these are master files, you could have environment variables to point to where the Excel files are and use the Excel connector IN yours Canvas App to read the data. I prefer doing it in Power Automate, but either way its possible.
Modification stage
Words to be ignored need to be removed from the input names.
Words that are often abbreviated need to be lengthened / shortened to ensure that both forms of the words are checked. For example, Limited is often shortened to Ltd. Any inputs that contain the word Limited or Ltd need to be checked with both versions / spellings.
Can be done in the canvas app or in Power Automate. I am trying to understand if this happen TO the data that was in the Report or what exact step in the process this is. I see the Stage Name but doesn't have inputs etc. But either way simple to do. Iterate through each row and make the change, OR technically... and I am saying its possible, you could do a global Change for all instances of the strings, but i'd need more details to gaurantee its safe use.
Checking stage
The Master files contain multiple sheets. User has a list of sheets that can be ignored when doing the search. Each modified input needs to be checked to see if it exists on any sheet (apart from the sheets to be ignored) in any of the three Master files.
Completely Possible in either the App or Power Automate, I would still use Power Automate its easier and intended for things like this.
Output stage
Output needs to show all modified inputs plus, if they were found in the Master file(s), which Master file(s) and on which sheet(s).
Completely Possible in either the App or Power Automate, I would still use Power Automate its easier and intended for things like this.
Then in the Power App (Canvas App) you show the report or whatever term you are using, but yes
This would be fairly simple in VBA (Excel or Access) but is it even possible in Power Apps? Any tips on how I could it if it is possible? I have looked at what function Power Apps has and I think things like If, Substitute, Lookup and a few others would be needed if this is at all possible in Power Apps. Could this be done using Power Apps?
It really depends on how this gets triggered for me to give you my 100% opinion. But personally I would use Power Automate for all of it and have it output the report data to SharePoint List and then have an App that can both push the list to modify etc (of names etc) and to read the report data and format it pretty on the screen.
Its hard to be step by step here without more details, but yes easy enough
If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others
Cheers
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey
