After my blog post from 2019 was met with great interest, I would like to present here the result that came out of it.
The idea of using Microsoft’s Power platform to create an app for recording and processing expenses is a good example to show what can be achieved with the available tools. However, there are also difficulties and limitations.
The thought of a custom mobile app that leverages employees’ existing hardware to facilitate expense tracking is tempting. All employees have an O365 E3 license, so nothing stands in the way. Or is there?
Warning spoilers, the licensing here will require some creativity to achieve our goal. In fact, with the M365 license, the Power Apps and Power Automate tools can be used, but with limitations. These tools need to communicate with other systems to accomplish the tasks. Data must be stored, Excel or PDF reports must be created, and an approval process must not be missing. Connectors are used for communication, which are divided into standard and premium connectors. As soon as my applications use a premium connector, the licensing with M365 is no longer sufficient for each user, then everyone needs their own licensing for Power Apps and/or Power Automate.
Back to the planned expense app: it should make it possible to scan expense receipts with a cell phone and enter the necessary attributes such as amount, customer, category, date, etc.
For this purpose, a SharePoint list with file attachments is available in M365. It is flexible, can be extended with any attributes and – very important – has connectors to Power Apps and Power Automate – which are classified as standard and can therefore be used with our license.
Thus, we have a data repository for the individual expense reports. Now we want to create a report at regular intervals that contains, for example, all expense receipts for a month and a table as an overview. Since these are documents, SharePoint with its document libraries is again a good place to store the documents created.
First, an Excel document with selected expenses is to be created. For this I use a template with an Excel table, which is copied and supplemented with the values of all expense receipts to be settled. The calculation of Total and other layout elements are defined in the template and can thus be edited later without any problems.
To enter the values into an Excel spreadsheet, I use Microsoft Graph API. However, there is a licensing problem with this. HTTP Request Actions are available in Power Automate, but they require a premium license.
This is where Microsoft Azure comes into play. In Azure, Logic Apps can be used, which offer the same graphical editor for definition as Power Automate. These do not require licenses and are billed based on the Azure resources required, which in this case are very small. How to trigger an Azure Logic App from Power Automate? HTTP request? No, you need premium licenses for that 😊.
The detour is via a SharePoint list. The trick is to use a list as a kind of job queue. So when a user wants to create an expense report, the Power App writes the job with all the information (which receipts, etc.) to this list. On the other hand, we build a Logic App in Azure that has as a trigger the creation of an entry in this list. In this way, we can indirectly realize the transition from Power Apps to Azure Logic Apps.
The Logic App can now make Graph API calls without licensing restrictions, and with some Graph API knowledge, we can get the job done. So our Excel is created and put back into a SharePoint library.
Our accounting department now wants, in addition to the Excel document, a PDF with the information from the Excel document and all the receipts of the expenses that we have captured in the app as images or PDFs with the individual expenses. Power Automate, but also Logic Apps offer some actions from well-known PDF producers like Adobe. You can do a lot with these. However, I found it difficult to do all the needed functions like resizing images, converting Excel to PDF, merging PDFs, numbering entries, etc. with Logic Apps functions. I decided to implement PDF generation myself in C# code. This code runs in an Azure Function and is called by the Logic App. There are several solutions, with or without programming.
Now the finished report still needs to be approved. This is where Power Automate comes into play. Approval Actions can be used to send the report to the supervisor or a central location for approval or rejection.
To keep the employee informed of the approval process in a timely manner, I use push notifications directly to the mobile device running the Power App. This connection is only available in Power Automate and not in Logic Apps, but can also be implemented by using a SharePoint list as described.
I see huge potential in Microsoft’s Power Platform tools. Business processes can be implemented flexibly, apps can be rolled out to cell phones, and all this combined with data security and compliance in M365 and Azure. Even though one hears again and again that these tools can be used without programming knowledge, it still takes some know-how to solve real use cases with these tools and to create added value. The dimension of licensing, which often determines whether a solution is profitable or not, is new and offers room for creative solutions.