Use a Power App app with Power Platform connector having an SAS Viya REST API endpoint (part 2)

[#1]

Introduction

In part 1 of this blog series we created a custom connector via the Microsoft Power Automate platform. These custom connectors give us the ability to connect to services that do not have a prebuild connector available. Through the custom connector that we build we are able to pull data from a table that is loaded into the SAS Viya Cloud Analytics Server. Now that we have access to data, let's see how we can now consume that data through a connector in an Power Automate Flow.

Creating the Power Automate Flow

Go to make.powerautomate.com and sign in with your Microsoft account. Select New flow and as a template select Instant cloud flow.

Give the flow a name and choose PowerApps as the trigger for the new flow

Click New Step.

We will be creating 5 different steps within this Power Automate flow and in between we will be executing a test from time to time. The steps in the flow are:

• Adding the custom connecter that we created in part 1 of this blog series
• Parsing the response. This step will process the response given by the custom connector
• Initializing a variable. This will hold the response for further processing
• Apply to each. This step will structure the data
• Response

Add custom connecter

Click on Custom and then try to find the custom connector that we created in the first part of this blog series

You should see the Action that we defined during the development of the custom connector. This action is called getRows

Click on the getRows action. Now you need to specify where to get the value of limit and start parameter. For the limit parameter click on the input box and click on Add dynamic content. From in the 'Add dynamic content' window choose 'Ask in PowerApps'.

Do the same for the second parameter called start. If you don't see the option Ask in PowerApps  you might need to click on See more and it should appear. Select that option for this parameter as well

Testing the Power Automate Flow

At this stage we should run a test as we need to have an example of the response in json format for the next step. Click save. After a successful save you should see  a message that your flow is ready to go.

Click Test and select Manually before you click Test again.

If necessary click Continue followed by Run Flow. Make sure you provide valid numeric parameter for limit and start parameter.

Validate if your flow ran successfully and if it ran successfully then copy the body of the output as you will need it in the next step. So make sure you have it in your clipboard or save it in a file.

Parsing the response

Go back to the flow definition and now you need to create new step. Search for the Parse JSON action.

The data to parse is the body from the previous step. You can select that again through the  Add dynamic content window

For the schema you should click Generate from sample. As the schema we are going to use the content that we copied from our validation run. Copy/paste the whole content in the Insert a sample JSON Payload screen as shown here below.

At this stage your flow looks like this.

Initialize variable

Now we are going to create an Initialize variable step. Make sure it's of type Array.

Apply to each

Now we need to loop over the items that were returned in the previous step. First create an Apply to each step.

Click in the field select an output from previous step and select items.

After that add an action for each item. So click on Add an action and select Append to array variable as operation.

Next step could probably be done more dynamic as we could request with another API call column names but here we coded them like this:

You can continue with all columns until you have this:

Adding a response

Add response step.

Select data variable from the Dynamic content.

Running another test to generate output

This is good moment to run another test as we want to copy the json output of the last step. You will need a sample output. Run the test like we did before and you should see an output like this:

Go the details of the last response.

And more specifically copy the output json.

Edit the response step

Edit the flow definition again. In the last Response step click on Show advanced options, followed by Generate from sample and paste the output json there. Click on Done to close the window.

You should end with this as Response.

The flow is now ready and it's good idea to run final test and validate if the expected data is the in Response, ready to be sent back to the caller.

When the test is completed, validate if your last test succeeded. Open the last run and check the response in the last step. It if contains valid data you can continue and start using this flow in a Power App.

Conclusion

In this blog we have created a Power Automate Flow that uses the custom connector that we build in our previous blog to fetch and process the data in a way that the Power Automate app is able to show the data. In our next blog we will walk you through the steps to build a Power Automate App that is capable of visualizing the data using the flow the we build in this blog.