Final Year Project - Microsoft Power Automate
by Ivan Jaben Pang
Student | Content Creator
Brief Introduction
This project aims to help Suntec Singapore cut manual labour by using automation to check and submit bill items for payment and document the process, limitations, and solutions.
I carried out an Additional Project where research is being carried out on Power Automate's compatibility with Azure VMs. Lastly, I aimed to re-design and Improve the Efficiency of the first flow I created before Week 6.
Brief Introduction
This project aims to help Suntec Singapore cut manual labour by using automation to check and submit bill items for payment and document the process, limitations, and solutions.
I carried out an Additional Project where research is being carried out on Power Automate's compatibility with Azure VMs. Lastly, I aimed to re-design and Improve the Efficiency of the first flow I created before Week 6.
Brief Introduction
This project aims to help Suntec Singapore cut manual labour by using automation to check and submit bill items for payment and document the process, limitations, and solutions.
I carried out an Additional Project where research is being carried out on Power Automate's compatibility with Azure VMs. Lastly, I aimed to re-design and Improve the Efficiency of the first flow I created before Week 6.
Bill Processing Payment Automation Project
Bill Processing Payment Automation Project
Bill Processing Payment Automation Project
Flow Adjustments
Flow Adjustments
Flow Adjustments
Taking a close look at the "Suntec Ap Aging Detailed report" Excel Worksheet, all of the relevant data instructed to be extracted from the Suntec Sandbox is compiled into the Excel Worksheet. Such as Vendor, Transaction Bill Type (Bill, Credit Bill, etc.), Supplier's Invoice Date, Document Number/Ref. No, Open Dalance, CER/PR
Taking a close look at the "Suntec Ap Aging Detailed report" Excel Worksheet, all of the relevant data instructed to be extracted from the Suntec Sandbox is compiled into the Excel Worksheet. Such as Vendor, Transaction Bill Type (Bill, Credit Bill, etc.), Supplier's Invoice Date, Document Number/Ref. No, Open Dalance, CER/PR
During the 6 Week's project duration, I've crafted two seperate Method of Data Extractions, Versions 1 & 2.
Comparing the Method of Data Extraction in Flows Version 1 and 2, we can see that Transaction Bill Type, Reference Numbers and CER/PR Types were previously extracted through web extraction. However, in the New and Improved Flow, all these critical data are extracted purely from the Excel Sheet, which is more efficient.
During the 6 Week's project duration, I've crafted two seperate Method of Data Extractions, Versions 1 & 2.
Comparing the Method of Data Extraction in Flows Version 1 and 2, we can see that Transaction Bill Type, Reference Numbers and CER/PR Types were previously extracted through web extraction. However, in the New and Improved Flow, all these critical data are extracted purely from the Excel Sheet, which is more efficient.
Comparing the Two Flows, we can see that in the first version, the Identification portion of the flow in Bill Processing Payment Version 1 is done after the Web Extraction of Data in Step 3.
Whereas, in Version 2, the identification process is done earlier while the list of Bill Items is being extracted, which is less time-consuming, due to the elimination of Web Data Extraction.
Comparing the Two Flows, we can see that in the first version, the Identification portion of the flow in Bill Processing Payment Version 1 is done after the Web Extraction of Data in Step 3.
Whereas, in Version 2, the identification process is done earlier while the list of Bill Items is being extracted, which is less time-consuming, due to the elimination of Web Data Extraction.
Project Limitations & Solutions
Project Limitations & Solutions
One Limitation that I faced was the inefficiency of the original flow. This issue would be met depending on the number of bills payable to a specific Vendor, as tabs would have to be opened and closed for data extraction.
One Limitation that I faced was the inefficiency of the original flow. This issue would be met depending on the number of bills payable to a specific Vendor, as tabs would have to be opened and closed for data extraction.
My solution to this issue, was to rely solely on the "Suntec AP Aging Detailed Report" Excel data which already consists of all the web extraction data. As data extractions from Excel sheets are faster and less time consuming, it is estimated that this new flow would be Twice as efficient as before.
My solution to this issue, was to rely solely on the "Suntec AP Aging Detailed Report" Excel data which already consists of all the web extraction data. As data extractions from Excel sheets are faster and less time consuming, it is estimated that this new flow would be Twice as efficient as before.
Another limitation would be the difference in Screen Resolution of a Virtual Machine. Due to some Power Automate Flow Actions depending heavily on the X and Y Coordinates of the Screen, the difference in Screen Resolutions would significantly impact how the flow is carried out. One example would be the use of the "Send Mouse Click" Action.
Another limitation would be the difference in Screen Resolution of a Virtual Machine. Due to some Power Automate Flow Actions depending heavily on the X and Y Coordinates of the Screen, the difference in Screen Resolutions would significantly impact how the flow is carried out. One example would be the use of the "Send Mouse Click" Action.
The workaround solution to this issue would be to Use a Remote Desktop Connection and adjust the Display Configuration setting to match the Screen Resolution of 1920 by 1080 pixels.
The workaround solution to this issue would be to Use a Remote Desktop Connection and adjust the Display Configuration setting to match the Screen Resolution of 1920 by 1080 pixels.
Power Automate Flow Understanding
Power Automate Flow Understanding
Firstly, Power Automate first checks if the current row of data it's reading is a Vendor's Item or not. You will notice that under the "No Vendor" section, all data in those rows do not contain vendor data. While under the "Vendor" Section, the next few rows will follow up with Vendor IDs and Names alongside their Bill Items.
Thereafter, Power Automate checks if the Data in that cell contains the words "Total" or "Vendor". This is to make sure that the Vendors ID and Name are extracted in their pure form.
Firstly, Power Automate first checks if the current row of data it's reading is a Vendor's Item or not. You will notice that under the "No Vendor" section, all data in those rows do not contain vendor data. While under the "Vendor" Section, the next few rows will follow up with Vendor IDs and Names alongside their Bill Items.
Thereafter, Power Automate checks if the Data in that cell contains the words "Total" or "Vendor". This is to make sure that the Vendors ID and Name are extracted in their pure form.
Power Automate then reads from Rows 'B' and 'E' to identify two key factors, such as the current row of data being a Bill Item and if it consists of a Supplier Invoice Date.
Power Automate then reads from Rows 'B' and 'E' to identify two key factors, such as the current row of data being a Bill Item and if it consists of a Supplier Invoice Date.
Power Automate Reads from Column F to extract the reference number of the Bill Item. If the Supplier Invoice Date meets the requirement, Data from Column O will be extracted. If the cell in Column O turns out to be empty, the item would then be recognised as a PR item. As such, the Bill Item's Vendor Name and Reference Number will be stored in the list with "PR" at the ending and Vice Versa.
Power Automate Reads from Column F to extract the reference number of the Bill Item. If the Supplier Invoice Date meets the requirement, Data from Column O will be extracted. If the cell in Column O turns out to be empty, the item would then be recognised as a PR item. As such, the Bill Item's Vendor Name and Reference Number will be stored in the list with "PR" at the ending and Vice Versa..
If a Vendor's Total Bill Payables result in $0.00 or lesser, all Bill Items consisting of that Vendor will be identified and removed from the list.
If a Vendor's Total Bill Payables result in $0.00 or lesser, all Bill Items consisting of that Vendor will be identified and removed from the list.
Video Demonstration
Video Demonstration
Machine Group Web Scraping Automation
Machine Group Web Scraping Automation
Machine Group Web Scraping Automation
Brief Introduction
This project aims to help Stakeholders of Cryptocurrencies by Web Scraping the Crypto's Market Cap price fluctuations and its Top 3 Biggest Gainers using a Hybrid of one Local VM and one Azure VM in a Single Machine Group. As such, Research on Machine Groups, Cloud Flows, and VMs are required.
Brief Introduction
This project aims to help Stakeholders of Cryptocurrencies by Web Scraping the Crypto's Market Cap price fluctuations and its Top 3 Biggest Gainers using a Hybrid of one Local VM and one Azure VM in a Single Machine Group. As such, Research on Machine Groups, Cloud Flows, and VMs are required.
Project Limitations & Solutions
Project Limitations & Solutions
Project Limitations & Solutions
My first limitation faced, was the Storage of Web Scraped data. Due to the different storage directories in various machines, it is challenging for Power Automate to store Files using a standard set directory across all machines.
My first limitation faced, was the Storage of Web Scraped data. Due to the different storage directories in various machines, it is challenging for Power Automate to store Files using a standard set directory across all machines.
My solution would be to utilise OneDrive, which is pre-installed and can be found in File Explorer across all machines. Utilising a Single OneDrive account, the platform can be used across all devices as a cross file-sharing platform on the cloud.
My solution would be to utilise OneDrive, which is pre-installed and can be found in File Explorer across all machines. Utilising a Single OneDrive account, the platform can be used across all devices as a cross file-sharing platform on the cloud.
The next limitation I faced was Local VM and Azure VM consisting of different credentials. Such is due to the non-similarities in "Domain and Username" and "Password", providing connection issues with cloud flows.
The next limitation I faced was Local VM and Azure VM consisting of different credentials. Such is due to the non-similarities in "Domain and Username" and "Password", providing connection issues with cloud flows.
One Workaround is to use Command Prompt to look for the SID and Rename the ProfileImagePath in the SID Folder using the RegistryEditor app. Thereafter, rename the file in Users and change the Virtual Machine's password to match credentials.
One Workaround is to use Command Prompt to look for the SID and Rename the ProfileImagePath in the SID Folder using the RegistryEditor app. Thereafter, rename the file in Users and change the Virtual Machine's password to match credentials.
Flow Understanding
Flow Understanding
Based on the Desktop Flow, Power Automate opens a New Excel Sheet and launches a new Chrome tab to show the CoinMarketCap website. After that, the data on today's cryptocurrency prices by market cap and its biggest gainers are extracted and written onto the New Excel Sheet.
Moving on, the Excel sheet will then be saved as "Top_Cyrpto_Gainers", which would be renamed by adding the current DateTime to the back of the file's name. The file will then be moved into the OneDrive Cloud folder, which can be accessed across all logged-in machines.
Based on the Desktop Flow, Power Automate opens a New Excel Sheet and launches a new Chrome tab to show the CoinMarketCap website. After that, the data on today's cryptocurrency prices by market cap and its biggest gainers are extracted and written onto the New Excel Sheet.
Moving on, the Excel sheet will then be saved as "Top_Cyrpto_Gainers", which would be renamed by adding the current DateTime to the back of the file's name. The file will then be moved into the OneDrive Cloud folder, which can be accessed across all logged-in machines.
As for the Cloud Flow, the flow can be manually triggered to run the previously described desktop flow based on the connected available virtual machines that are online. If the flow has run successfully without any errors, the email shown on the left will be sent. Otherwise, the email on the right would be automatically sent to notify the user.
As for the Cloud Flow, the flow can be manually triggered to run the previously described desktop flow based on the connected available virtual machines that are online. If the flow has run successfully without any errors, the email shown on the left will be sent. Otherwise, the email on the right would be automatically sent to notify the user.
Video Demonstration
Video Demonstration
