Power Automate: Get list of values for Power Virtual Agents’ List Variable

My last article was about some of the new preview features in Power Virtual that we can try out today. One of these features is the ability to bring in dynamics options from Dataverse and store these in a list variable in Power Virtual Agent to show as options when the bot asks a question. Let me explain what I mean by that. Previously when we asked a question in Power Virtual Agents and we wanted to offer a list of choices to the customer to pick from, we could show a list of multiple choice options to pick from, but the values in those lists are static or hardcoded within Power Virtual Agents. With this new feature called ‘Options from a list variable’, we can bring lists of options from another application into Power Virtual Agents by calling an action, meaning that the options we show to the customer in chat will be dynamic!

In last week’s article (which you can access here) I showed how to use options from a list variable once Power Automate returns the list of items. In this article I am going to discuss how I built the Power Automate flow that gets the list of items from Dynamics 365 Customer Service and feeds it back to Power Virtual Agents.

Since I don’t need any input from the Power Virtual Agent at this point (I’m just getting values from my custom ‘drop down values’ table) the input in the first (Power Virtual Agents) step stays blank. (for details on the drop down values table please read last week’s article). In the second step I am going to initialize an array variable which will be used later to store the data after I query Dynamics 365 Customer Service to get the drop down values. I call the variable ‘DropDown’ but you can use any name for this.

In the next step I use the ‘List Rows (legacy)’ step. Please note I have to use the legacy step because my Power Virtual Agents lives in a different environment than my Dynamics 365 Customer Service instance. If they both live in the same instance you can use the other Dataverse connector. I select the environment I want to use in the ‘Environment’ field, and search for the ‘Drop Down Values’ table in the Table Name field. In the filter query I enter (statecode eq 0 and _new_parent_value eq null). This means that I am filtering the data to only show active rows, and rows that don’t have data in the the parent field. (Child rows/sub category rows will have the parent categories populated in the new_parent field, so filtering this way allows me to only bring in the ‘Parent’ categories from the Drop Down table.

In the next step I am going to ‘Append to array variable’, which means I am going to get the results of the query from the ‘List Records (legacy)’ step and store them in the (array) variable that we created earlier. Here I have to select the variable I initiated earlier (called DropDown) in the name field, and for the value set the ‘Name’ field from the ‘List Records (legacy)’ step. This is coming from the Dynamics Values option in the flow. What we’re doing here is only grabbing the name of the row, as this is what we want to feed back to Power Virtual Agents later. When I select the Dynamic ‘Name’ field, an ‘Apply to each’ step is automatically added around the ‘Append to array variable action’, this is because there will be multiple rows coming in, so this is exactly what we want to happen.
The next steps took me a while to figure out, and this might not even be the best way to do this, but it got the job done! Since I didn’t have the ability to feed the data from the variable back to Power Virtual Agents in the last step at this point, I needed to figure out a way to do this. I created a compose action (called Compose 2) to turn the array values into a string and entered the following expression:

string(variables('DropDown'))

This almost saved my problem, but I noticed some additional characters were added to the output: an opening square bracket at the beginning of the list and a closing square bracket at the end of the list and each row had an opening double quote and a closing double quote. Below is what the output looked like in the Compose 2 step, with the double quotes and the square brackets.

["Inquiry","Service","Password reset needed","Billing Issues","Product Issue"]

In order to remove these extra characters I used one more compose step (called compose) where I entered another expression. You can see I am removing all double quotes by replacing them with nothing, then I’m doing the same with the opening square bracket and the closing square bracket. Below is the expression and a screenshot of the compose steps.

replace(replace(replace(outputs('Compose_2'),'"',''),'[',''),']','')

In the last step I will feed the data back to the topic in Power Virtual Agents so here I select the output from the last compose step in the flow. Keep in mind the data going back to Power Virtual Agents is a string, meaning that this is one long line of text, containing all the drop down values, separated by a comma. The good news is that we can separate the individual rows by using a PowerFX formula in Power Virtual Agents (which I show in last week’s article. I hope you found this article informative! Be sure to check in again next week for a new article or subscribe here to never miss another post!