Extending PowerApps with Azure Functions

extending-powerapps-with-azure-functions

Recently we’ve been building a Capex system for one of our clients which needed to replicate their existing system written in Excel. One of the key features of PowerApps is that it is designed for people to easily transition from writing their applications in standard Microsoft products such as Excel into PowerApps, so includes many of the Excel functions. Two functions the Excel version of their application were NPV() (Net Present Value) and IRR() (Internal Rate of Return).

Neither of these functions is available within PowerApps.

The formula for calculating the NPV was simple enough and could be translated into a single formula:

Unfortunately, the function IRR is much more complex than that and is used to calculate the interest rate required to get a 0 NPV value.

Thankfully, there is a Visual Basic function available to calculate the IRR, Azure Functions can implement VB functions, and PowerApps can implement Azure Functions…

The Azure Function

Creating the Custom Connector

Updating your PowerApp

The Azure Function

The first thing to implement is a new Function App to hold the calculation:

Start by creating a new Function App using the .NET Runtime Stack:

Once provisioned, create a new Function with a HTTP Trigger. For this demo, the service I’m creating is going to allow anonymous users.

The code I’m using to calculate the IRR value is below. Essentially, reference Micorosft.VisualBasic.Core, create an input and output object and the Run function:

#r "Newtonsoft.Json"
#r "Microsoft.VisualBasic.Core"

using System.Net;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Primitives;
using Newtonsoft.Json;
using Microsoft.VisualBasic;

/* Data we're accepting */
public class ValuesObject { 
    public double[] data {get;set;}
}

/* Our return object */
public class IRRValue {
    public double IRR {get;set;}
}

public static IRRValue Run(HttpRequest req, ILogger log)
{
    /* get the values object from query string */
    string values = req.Query["values"];

    /* if no value comes from the query string, try getting it from the request body */
    if(string.IsNullOrEmpty(values)){
        string requestBody = new StreamReader(req.Body).ReadToEndAsync().Result;
        values = requestBody;
    }

    /* deserialize the object */
    ValuesObject data = JsonConvert.DeserializeObject<ValuesObject>(values);

    double[] myData = data.data;

    try {
        /* execute the IRR function */
        double tmpIrr = Microsoft.VisualBasic.Financial.IRR(ref myData);
        log.LogInformation("IRR: " + tmpIrr);

        return new IRRValue { IRR = tmpIrr };
    } catch {
        /* if it fails, just return 0 */
        return new IRRValue { IRR = 0 };
    }
}

Once you’ve implemented your code, save it and use the Test/Run feature to check that the code works.

Creating the custom connector

The process for creating a custom connector to link with the Azure Function is relatively simple once you know the process.

Within your PowerApps environment, expand Data and select Custom Connectors from the navigation.

Provide the connector with a name (I used “Finance”)

Gathering the information for your connector:

Retrieve the Azure Function’s URL from Azure from the function’s overview page using the “Get Function Url” button

The host is the hostname from the url: amt-irr-calculator.azurewebsites.net

The base url is the rest of the url: /api/IRR/

Click next and as previously stated, I’m using no authentication:

Click next and complete the definition of the connector.

Create a new action:

For simplicity, I set the summary and operation Id to “IRR”

Then, import the request from a sample:

The sample I imported into the form was the same details I used to test the function in Azure:

Finally, add a default response

The data imported was copied directly from the Azure Function test result:

Once the action has been completed with the above information, use the “Create Connector” button to create it and close to go back to the custom connectors screen.

From here, use the + to create a new instance of the connector so it can be tested.

Once created, head back to the custom connectors screen and edit the connector again, skipping to page 4 to test.

Switch the toggle over to “Raw Body” and paste in your testing data:

Testing the operation should result in: 

Finally, head to the connectors screen and use the share function to share it with whoever’s going to be using your PowerApp:

Updating your PowerApp

To use the function within your PowerApp, you’ll have to create a new connection to the connector:

For this example, I’m creating a new collection with my financial information on screen visible:

On my screen, I’ve added a data table to display my values, a button to trigger the function and a label to display the output:

The button’s on select function updates a context variable with the response from the IRR function:

Finally, the label’s text function is set to the output IRR value:

Clicking the button executes the function and updates the label’s text value:

Just before clicking the button yourself, open the code view of your Azure Function and stop and start the logging at the bottom. Then, when you click the button, you should see the event be triggered: