BookmarkSubscribeRSS Feed

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

Started ‎07-12-2023 by
Modified ‎06-28-2023 by
Views 787

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.

 

image16.png

 

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

image17.png

 

Click New Step.

 

image18.png

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

 

image19.png

 

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

 

image20.png

 

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'.

 

image21.png

 

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

 

image22.pngimage23.png

 

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.

 

image23_v2.png

 

Click Test and select Manually before you click Test again.

 

image23_v3.png

 

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

 

image23_v4.png

image23_v5.png

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.

 

image23_v6.png

 

Parsing the response

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

 

image24.png

 

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

 

image25.png

 

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.

 

image26.png

 

At this stage your flow looks like this.

 

image27.png

Initialize variable

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

 

image28.png

 

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.

 

image29.png

 

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

 

image30.png

 

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

 

image31.png

 

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:

 

image32_0.png

image32_1.png

 

You can continue with all columns until you have this:

 

image32.png

Adding a response

Add response step.

 

image35.png

 

Select data variable from the Dynamic content.

 

image36.png

 

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:

 

image33.png

 

Go the details of the last response.

 

image36_b.png

 

And more specifically copy the output json.

 

image36_c.png

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.

 

image37.png

image38.png

 

You should end with this as Response.

 

image39.png

 

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.

 

image40.pngimage41.png

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.

Version history
Last update:
‎06-28-2023 04:28 AM
Updated by:
Contributors

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Labels
Article Tags