How to Build a Trade Approval App with Microsoft's Power Platform

We’ll start by looking at the Microsoft Power App maker portal make.powerapps.com, and we’re going to start by building a data flow to go and grab our data from an external source and bring it into Microsoft Dataverse.

Now, these trades might be coming from some external service, an external application, and we might want to go ahead and import that just so that we can use this data. More sophisticated applications might be able to pull this from a variety of internal systems or may have this dumped somewhere. Make sure you’ve selected your right environment. Otherwise, you’ll be importing this data into an environment that maybe is not the right one for you. We’re going to assume that this is coming from some external source, so show you how we can pull this data in quickly. We’ll start with data flows and from blank. I’m going to give this a name. Go ahead and click create.

Microsoft Power Platform Connectors

One of the benefits of the Microsoft Power Platform are all these different connectors that we have for pulling this data. We could pull this from maybe from Microsoft Exchange, from Azure storage, from Excel or text file.

I’m going to choose a text file for today. I’m using Microsoft OneDrive as the location, so this might have been dropped by OneDrive folder, it could be dropped to a Dropbox or some other external source. I’ll go ahead and browse OneDrive and I have an imports folder and a trades folder, and it should be right in there where I can pick my CSV file.

Data Preview

I’m going to click select and next, and this is going to give me a preview of the data that needs to be transformed, but it’s not going to actually transform the data. Just doing a quick read of that data, it shows me the data so I can get a quick view of it. Looks good. I’m going to go ahead and transform the data.

Microsoft Power Query Example

This will open up the data in Microsoft Power Query. Now for those of you that are familiar with Power BI or maybe in advanced Excel reporting, you may be familiar with Power Query. It’s a tool that allows us to transform the data and build a series of steps that we want to perform against it. I’ll give you an example now.

If we look at the data, most of this data is text and you can see by the icons that this has been already formatted as text. But as we go and look at the remainder of our data, our amount has been formatted as currency, which is perfect, but our share quantity has been formatted as a whole number. We want to go ahead and change that to a decimal number. Now because it’s already done some steps to transform it properly, it’s going to ask me if I want to replace this and I’m going to say yes.

You’ll see a series of steps that have been applied. We read our source, it promotes the headers above, it changes the column types. What’s nice about these is that these steps get saved and applied every time. Each time a new version of this file gets dropped, it will apply these exact same steps and we don’t need to go ahead and manually save these or reevaluate these.

Now that I’m clicking next, it’s going to ask me where I want to store this. This is the very first time I could load this into a new table, it’ll auto generate some fields for me and some different data types. You see, for example, amount is already a currency and share quantity is a floating-point number. I’ve already got a table for this, so I’m going to select the table as my trade approvals table.

All we need to do is go ahead and map the fields. You may find that there are some fields in here that we do not need to map, like the approval comments. Those we do not have yet. Once again, this is one of those things that only needs to be done once and this will get saved as part of the mapping. You won’t have to come back and do this again.

I’m going to click next once I’ve reviewed this and approved it, and now I get to choose how I want this to be refreshed. Most organizations, this is not something that’s done on a one time basis, but it could be. For testing, maybe I only want to refresh this manually, but most of the time we want to set this to refresh, let’s say, every 60 minutes. We could pick days or hours. We could also set up a schedule to refresh only on specific days. I absolutely want to get a failure notification if this does not work for some reason.

In most cases, I click create. I don’t want to run this just yet. I’ll show you one that we’ve already created. Our trade approval CSV, this is the one that we just created through our process. What’s nice about this is even though this has been created for us, we can go and edit and run through that wizard, maybe change some steps if we need to make some changes.

We can rename it. We can also manually refresh it or edit our incremental and other refresh settings so that we know what’s coming in and we can choose if we need to have it run more frequently or less frequently.

Microsoft Dataverse

Now we have this data loaded. Let’s go and see where we can look at this, and that’s going to be in Microsoft Dataverse.

We’re going to view that through a web-based application. Here is a list of all of my trade approvals, so I’ve forgot this filter, let me say active. These are all 47 rows that have come in. You’ll notice that there are certain fields that we didn’t automatically map. This is our approver and maybe even our approval status. We have some logic that’s running behind the scenes that’s going to go ahead and pull this in and may automatically assign these based on who the client is or what the relationship is, what the ticker is, if it’s part of portfolio.

We can also pre-approve some of these based on the share quantity. If the quantity of the amount meets certain threshold, we may be able to pre-approve it without having to send this to an individual. We’re going to go and look at one that’s specifically been assigned to me where it’s greater than a thousand shares, and I’ll open up that record just to give you a sense of what it looks like. We have our trade information, our client, we get our trade details, what security, how many shares in the total amount, and then we have our approval information. Now, I could come in and update this information right here on the screen, but more than likely, I’m out of the office, I’m on the road, and we don’t want to have a period of time where we’re waiting for someone to do this.

We’re going to want to send a notification to them if an important trade comes in, let them get this on their phone and go ahead and approve it right there. We’re going to do that using Power Automate and a little field here that I’ve called push notification, just in case we want to change the logic, maybe for specific individuals or for specific thresholds, I’ll have the logic that operates behind the scenes. Once it operates behind the scenes, we’ll use this push notification to trigger whether the user’s going to get that notification, so let me show you how we’re going to do that.

We’re going to go into our Power App maker portal again, make.powerapps.com. This time click on flows.

Power Automate

Now I’ve already got a trade approval push notification, so I’m going to ahead and edit that one.

With our Power Automate, it asks us for a trigger, so the trigger is coming from Microsoft Dataverse when a record is added, updated, modified, or deleted, and I’m going to say when the trade approval is updated, then we’re going to check for a condition is push notification se equal true. If it is, we’re then going to go ahead and send a push notification, so it’s going to open up the trading app, we’ll give it the message, we’ll tell it to open the app to this entity, to this form, and to this record.

Once we’ve sent that push out, we then want to go ahead and update the record and we’re going to turn that push notification to off just in case we want to send that to that person again. Let me show you how this works in the real world. We’re going to test this. Now if we’re in the real world, this would be running automatically behind the scenes. But just so you can see how it works, we’re going to click test.

I’ll give it a second. Now we can see that it’s waiting for an action to happen. I’m going to go back into my trade approval. I’m going to say yes and click save.

I just got a notification here on Windows and now I see the status.

This was successfully completed, and I can see that the expression was true, that the notification was sent and then eventually that this row was updated so that that push notification will be sent off.

Now let me show you what this looks like from the context of the phone. At the top of my screen, I see my notification with the name of the person, ticker symbol, the number of shares. I’m going to go ahead and click that to open it.

It’s going to open up my Power App and automatically drop me right into that form, where I can see all the different trade details, what came from this, how many shares were there. Then I can go ahead and say “Yep, I’m going to approve this.” I’m going to choose my approval reason; I’ll pick a date and I can put some comments and I could use Google or Siri or any of my other assistants to go ahead and help with this. Now I’m going to go ahead and scroll back up to the top.

Save this, record has been saved and I’m done. Let’s look at what this looks like back in Dynamics here and do a quick refresh and we’ll see that this is now approved with my notes and the approval status, and it’s that simple.

Power BI

Now, if we wanted to go and look at all of these, I’m going to take this data directly out of Microsoft Dataverse, and we can have a direct link of that data into Microsoft Power BI.

I’m going to go to my Power BI report, and I’ve taken all the data that we have, our 47 approvals that are in process, brought them in here, and we have a grid of this. We’ve brought over some of the key table indicators or key fields that we need, like our client, our trade type, our transaction type, order type cetera.

We’ve gone ahead and created some slicers, so this is going to enable us to go and filter the report by individual fields or columns. We can do it by ticker, and that’s a lookup by order type. We can also go and see the status of approvers and approvals, who it’s assigned to, and what the status is of those approvals. These will also act as filters. I go ahead and I select Richard, and that’s going to narrow our scope. Maybe we’re only concerned about those records that are over a thousand shares. I can go ahead and slide my share quantity up to just close to a thousand right there. That’s going to pre-filter my report.

Now I can start to go in and look and see the status of my individual records. I see this one for 1381 shares for Marco Lucia has been approved, and I can see the approval comments.

If I need to make any additional changes or see additional details, I can click the link icon and that will direct the user, assuming they have access, right back into the application in Dynamics or Power Platform and enable them to go and access this information and within reason, be able to go ahead and edit it.

I can also go through here and see if I have another approval that is assigned to me that I have not gone ahead and approved. I can click that go in and quickly approve that.

This becomes very helpful for the person that is responsible for approving the individual trades, but can also be helpful for anyone who needs to go and review the status of these, look at the status of individual reviewers. I want to go back and check on Adolfo and see how he’s doing.

I see he’s got a number of these outstanding and I can reach out to him, or we can fire off those notification pings to go and send additional notifications to their phone to let them know that they need to go and approve these trades. Hope that this has been helpful. We look forward to working with you and answering any questions you may have. Have a great day.