Create a London Underground Line Status Bot using Power Virtual Agents & Azure Management API in Dataverse for Teams

There has been an exciting development/game changer with the new Dataverse for Teams now allowing users to leverage the Azure API Management service as a custom connector in the Power Platform. This means with the custom connector you can call the Azure Management API which can then run an Azure Function which can run code i.e. PowerShell. The automation possibilities are endless and all using standard Teams licensing whereas before you would need premium licences to run some PowerShell in Azure to make a Microsoft Graph call or execute a PnP PowerShell script for example.

Step Summary

1. Enable Dataverse for Teams on an existing Microsoft Team
2. Create an Azure Function App in Azure
3. Add a Function to your Function App
4. Create an Azure Management API service
5. Add Azure Function to Azure Management API
6. Export API (Azure Function) as a custom connector to the Power Platform to your specified Dataverse for Teams environment
7. Build a Bot using Power Virtual Agents using your custom connector in Power Automate all in your Dataverse for Teams environment
8. Test the completed London Underground Tube Bot

1. Enable Dataverse for Teams on an existing Microsoft Team

A Dataverse for Teams environment is automatically created for the team when you create an app or bot in Teams for the first time or install the Power Apps app from the Teams app catalog for the first time. The Dataverse for Teams environment is used to store, manage, and share team-specific data, apps, and flows. Each team can have one environment, and all data, apps, bots, and flows created are stored in that team’s Dataverse for Teams environment.

In my case I want to use Power Virtual Agents to create a bot in Microsoft Teams. Add the Power Virtual Agents app to your Teams client by selecting Apps on the left hand menu and then selecting Power Virtual Agents

Select Add or Open (if you have previously already installed Power Virtual Agents).

Select Start now to create a chatbot

Select the Team you wish to add the Chatbot and this will be where you will use the Chatbot from within this Team in the Teams client or web client.

Confirm which Team the bot is joining by selecting Continue. Note if you are the first person to create a bot in the Team it will be a little longer than usual (in my experience this delays is usually 2-3 minutes).

Enter the name of the bot you wish to create i.e. Tubey, select the language and then click Create.

Now the Bot has been created we are going to leave this now as we will return to this later.

The Dataverse for Teams environment has been created. We can go to the Power Platform admin center (https://admin.powerplatform.microsoft.com/environments) and see the environment we just created – note the Type is Microsoft Teams. This is how we know this is a Dataverse for Teams environment.

Note: The Dataverse for Teams environment for a Team can also be created by adding the Power Apps app to your Teams client and then selecting Create a app – this Power App can then call the custom connector we are going to create.

2. Create an Azure Function App in Azure

You will now need an Azure subscription or to have signed up for an Azure free trial.

We will now create an Azure Function app by going to https://portal.azure.com and selecting Create a Resource.

Type Function App and then select Function App from the dropdown.

Select Create

Select the appropriate Subscription, Resource Group, Function App name, Runtime stack: PowerShell Core, Version: 7.0 and the Azure region nearest to you i.e. in my case UK South. Next click Next: Hosting

Next accept the defaults for the Hosting tab unless you want to change the Storage Account name like I did by selecting Create new. Once complete select the Next: Monitoring button

I then selected No to not enable Application insights (as this is a development subscription) and then selected Next: Tags > button.

I didnt want to apply any Tags to my Function App so skipped this screen and selected Next: Review + Create > button.

Review the settings for the Function App and then select the Create button.

Wait until the Function App has finished deploying and then navigate to you Function app.

3. Add a Function to your Function App

We will now add a Function to our function app – within this serverless Function we can run some code in my case PowerShell. Navigate to your function app and then select Functions from the left hand menu.

Select + Add

In the Add function window select the HTTP trigger template then either personalise the Template details section i.e. change the New Function name or accept the defaults. Then select the Add button.

The Function is then created – select Code + Test from the left hand menu.

You will now see a code window where you can enter any PowerShell code that you wish.

Which is amazing you can run PnP Powershell to make some changes to SharePoint or Teams etc. There are lots of other PowerShell modules or commands that you could use i.e. Microsoft Graph.

Use PowerShell to do a HTTP call to a webservice instead of having to use the premium HTTP connector in Power Automate.

I also find PowerShell better for doing advanced sorting, manipulation, calculations than having to do this all in Power Automate.

In my case I will replace the existing PowerShell that is there already with my PowerShell in the script editor below. You can use this code below to to build a custom connector to replicate a HTTP connector in Power Automate all under Dataverse for Team licensing (seeded).

The objective of my script is to retrieve live Line Status from the London Underground line status API and return the line status JSON to the user. There is also the ability to specify a particular line and only that Line Status is returned or otherwise all line statuses are returned. Click Save.

We can now click Test/Run and test the PowerShell we’ve just entered. In the input box on the right hand side add a Query parameter called LineName and add the value “District” (or any other London Underground line name). Then select the Run button.

You can now see the JSON returned on the right hand side for the District line and it has “Good Service”.

4. Create an Azure Management API service

Now we will create an an Azure Management API resource. Go back to the Azure Portal home page https://portal.azure.com/ and click Create a resource.

Type API Management and then select API Management from the dropdown.

Confirm API Management is displayed and then click the Create button.

We will now enter details for the API Management resource we wish to create. Select the Resource group we previously created London_Underground, the Azure region required (nearest to you), give the resource a name and then enter your organization name and administrator email for system notifications. Finally select the Pricing tier where I will select Developer (no sla) tier for this proof of concept then select the Next: Monitoring > button.

I have turned off Application insights and then clicked the Next: Scale > button.

We will then see a message that Scale units are not supported for Developer and Consumption tiers which is ok for us – select the Next: Managed Identity > button.

For this demo we will not enable System assigned managed identity but we may want to enable it to use Azure Key Vault or authenticate to via role based permissions to other Azure resources. Select Off and then we can click the Review + create button as we do not need to configure Virtual Network, Protocol settings or Tags

Ensure the details are correct and valid then click the Create button.

The deployment of the API will then take place – it took around 45 minutes for the deployment to take place and I think this is quite common from reading up. So time to take a break.

5. Add Azure Function to Azure Management API

We will now add an Azure Function to the Azure API Management service you just created or you will be using an existing API Management service.

Navigate to your Azure API Management service and then click on the APIs menu link on the left hand side and then click on Function App to add a new API.

Next a Create from Function App inline pop out will appear – click Browse to select your Function App.

Click on Function App Configure required settings

Select your Azure Function App in my case it is London Underground and then click Select.

Select the Azure Function you wish to import and then click the Select button.

The details for your Function App will then be displayed. I recommend that you change the Display Name, Name and API URl suffix to be something descriptive as this will be used as the Connector in Power Automate/Power Apps. Once complete select Create.

You now need to obtain your Azure API Management subscription key – this protects the API call from being called anonymously. Select Subscriptions from the APIs section on the left hand menu. Then in the Built-in all-access subscription row select the elipses (three dots) on the far right and then select Show/hide keys from the dropdown then copy the Primary Key value (make a note of this i.e. copy this to Notepad) now it has been revealed.

Now reopen the API we just added. Select APIs from the APIs left hand menu section then select Get Line Status or the name of the API if you renamed it.

Select the GET operation for your function i.e. HttpTriggerLineStatus and then in the Frontend section select the pencil icon to open the form-based editor

Select Query and then select Add parameter to add a query parameter.

Add a subscription-key Query parameter as Type String and select the Required checkbox to ensure it is required.

Add a LineName Query parameter as Type String and then click Save.

These Query parameters will then be requested by Power Automate/Power Apps when using the Custom Connector.

6. Export API (Azure Function) as a custom connector to the Power Platform to your specified Dataverse for Teams environment

Open the API you wish to export – first a bit of housekeeping to delete any unnecessary operations i.e. the POST operation that is not required. Delete any not required Operations.

Now all the unecessary API operations have been deleted – right click on the API you wish to export (to the Power Platform) then select the elipses and then from the dropdown select Export.

Select the Power Apps and Power Automate button.

Select the PowerApps environment to publish to i.e. London Underground and the name of the API to publish i.e. Get Line Status.

7. Build a Bot using Power Virtual Agents using your custom connector in Power Automate all in your Dataverse for Teams environment

Your custom connector should now be available in your Dataverse for Teams environment you created i.e. my Team was called London Underground. The custom connector created can be used directly in Power Apps and Power Automate via Power Virtual Agents.

My demo is going to focus on creating a bot using Power Virtual Agents and then using the created custom connector in Power Automate to make a HTTP call to the London Underground line status API. The bot will then use this to give information about the line status of the chosen London Underground line.

You can quickly see if the custom connector is available by going to Power Automate and switching to the appropriate Dataverse for Teams environment. Then creating a an instant Flow and then adding an action. Then selecting custom and you should then find your custom connector available that was created in Azure Management API.

_{However under the terms of the Dataverse for Teams licensing you cannot use this Power Automate Flow on it’s own as it has to be called from the Teams app or Teams web client so we will delete this Flow after testing.}

We will now go back to the Teams application and on the left hand menu click on the ellipses to add an App then type Power Virtual Agents then select the Power Virtual Agents button.

Next go to the Chatbots tab

Select the Teams chatbot you created earlier.

Select Go to Topics

Turn off all the example User Topics by turning off the Status control for the topic (alternatively you can use the as starter topics and modify them to your scenario). Then select New topic.

Enter a name for the topic i.e. Line Status and then add several trigger phrases (how customers might ask about this topic – see examples in image below) and then click the Go to authoring canvas button.

The topic will then be displayed in the topic editor UI. Add a message to greet users for the topic to the default message box i.e. I’d be glad to report the Line Status for your chosen line.

Next select the plus symbol underneath the Message box and then select Ask a question.

Add a question to the first box of the Question action i.e. “Which line are you interested in?”, then in the identity drop down ensure User’s entire response is selected.

Add a informational message i.e. “Obtaining results from the TFL API now…”

Next add an action to call Power Automate by adding an action then selecting Call an action then Create a Flow from the dropdown.

Select Power Virtual Agents Flow Template.

Next in the first trigger action named Power Virtual Agents – add an text input called LineName and add a description i.e. Please enter your line.

Next below this action insert a new step. We are going to add the custom connector we created earlier here. Select the Insert a new step button, then add an action then in the custom category select your custom connector i.e. Get Line Status.

Next select the required action in my case HTTPTriggerLineStatus. You may have created many different actions i.e. different API calls in your API (using Azure Functions).

Next add your Azure API Management subscription key  to the subscription-key field (you took a note of this earlier and is unique to your Azure API Management environment) and under the LineName column add the LineName field from the Power Virtual agents action.

Next add another step under HTTPTriggerLineStatus – this time we are going too add a condition. This will be used to detect if the LineName entered is valid i.e. spelt correctly or valid. Go to the first field of the condition and add the following expression below (this will be the name of the custom connector above – remember no spaces.

Next in the change the dropdown to is not equal to and add the expression string(‘null’) to the final part of the condition. String null is a bit of a dirty condition but works in my instance as the JSON array actually returns the word null as a string.

Next we are going to add a new variable for LineStatus near the beginning of the Flow. This will be used to format the LineStatus in nice Markdown format. Add an initialize variable action.

In the Yes section of the condition add an Apply to each loop, then add the expression body(‘HTTPTriggerLineStatus’)[‘lineStatuses’] for the input for the loop.

This loop is added as the line can have many different statuses i.e. Good Service, Part Closure, Severe Delays etc. We will then add an append to string variable LineStatus in the loop which will collect all the different Line Statuses.

Next add an append to string variable action, select the LineStatus variable and then in the value section add the expression item()[‘statusSeverityDescription’]

Next add a comma after the expression and a SPACE. This is so each status is separated by a comma and a space and not all as joined up words.

Next in the No section of the condition add a Set a variable action and ensure it is set to LineStatus. Then in the value section and add a message in the value section i.e. LINE NOT FOUND. This will be for when someone enters a misspelling or an incorrect value for the line name in the bot.

Next add a compose action at the bottom and outside of the condition. This is going to formulate in MARKDOWN language the Line Name in BOLD and then the Line Status underneath.

Steps in the compose action

1. Add # symbol
2. Add LineName (from the trigger action)
3. Press enter (new line)
4. Add the LineStatus variable

Next in the Return values to Power Virtual Agents add a Text output, named with the title LineStatus and then in the value section add the Outputs from the Compose action we just created.

The completed Flow should look like this – finally click Save and the click Back to go back to the Topic.

Now back in the Virtual Agents Topic add a message action

Insert the variable LineStatus into the message

Now Save the bot and we can also click Test bot to test the Bot. Next click back.

Now select the Publish button to publish the bot.

Next click on the Publish button (bot needs to published at least once before opening or sharing the bot) and then select Open the bot (to open the bot in Teams).

Select add button to add the bot to Teams.

8. Test the completed London Underground Tube Bot

Now in your Teams client you will be able to engage with the bot.

Type “Line status” to trigger the topic (or one of the other trigger words). The bot will then respond with a welcome message along with a question of which line are you interested in. Type the exact name of the line and then press enter.

In this demo (proof of concept) you will need to enter the exact name of the line but you could make the bot much more intelligent with branching and choices so users dont need to type the exact line names etc.

The topic will then call the Power Automate flow – which then calls the custom connector to connect to the London Underground line status API and then the latest line status for the requested line will be returned

Summary

This blog has introduced a great new feature of Dataverse for Teams to be able to call an API from a Teams App (Power Apps) or Teams Bot (Power Virtual Agents). This opens up many extensibility options extending Power Apps or Power Virtual Agents running Power Shell to call external APIs or even just PnP PowerShell to make changes to the SharePoint site of the Team.

The most exciting use case I can think of at the moment however is building a bot using Power Virtual agents to call externally hosted APIs and providing live content to the bot users i.e. querying live transport information, providing live information i.e. sports results etc. All using Dataverse for Teams licensing and not requiring Power Platform premium licences!

So this was the inspiration behind this blog using my familiar London Underground theme & using the Transport for London API to then build a tube bot that users can engage with to get live line statuses for a particular line. A super interesting research project and has now opened my eyes to lots more potential real world usages!

As usual let me know if you have any comments or questions in the comments below. It would be great to hear if you used this in your organisation?