Power Automate: Get Random Row from Excel

Power PlatformMicrosoftPower Virtual AgentPower Automate

Mar 15

Why did the Power Automate and Power Virtual Agent get along so well?

Because they had great "workflow" chemistry and always knew how to "bot" up each other's spirits!

Recently, I have had the need to get deep into Power Automate because I have been working on Power Virtual Agent where I need to utilize data from another application. I’ve been enjoying this journey into Power Platform.

One of the things that I was asked is to create a Power Automate Flow that would generate a random result from a file and return the value back to Power Virtual Agent. I had a hard time finding a good article that walks you through it, so I decided to put something together based on my scenario.

With my example, I will use a Dad Joke Randomizer that team members have populated throughout the years into an excel file. And I would like Power Virtual Agent to look up random Dad Jokes from the file whenever someone ask for a Dad Joke. During this walkthrough, I will be focusing on how you would create the Power Automate flow and less on Power Virtual Agent, this will be for future blog posts.

To get started, click the Create button and choose Automated cloud flow.

Microsoft Power Platform: Power Automate

Now let’s give a name to your Power Automate flow. Since this is a Random Dad Joke flow, I’ll give it a simple name of “Dad Joke Randomizer”. Since I am going to be using this with Power Virtual Agent, I will use the Power Virtual Agent flow trigger, “When Power Virtual Agents calls a flow”.

Then click “Create”.

Once you create the Power Automate Flow, it will look very empty. So the next step for us is to populate an input, Identify the Excel file where the Dad Jokes are, Initialize variable, Get a random row from an Excel file, Compose the result, and Return value(s) back to Power Virtual Agent!

First step is to initiate the input from Power Virtual Agent, with my example, I gave it “DadJokePrompt”, ensure it is ‘Text’.

Microsoft Power Platform: Power Automate - When Power Virtual Agents calls a flow

Next, we’ll need to add “Next Step” to get the file we to use. Search for “List rows present in a table”. Provide the necessary information of where the file is located. In this case, the file is located within a group’s document folder called DadJokes.xlsx. I would also suggest renaming the step to something easier. So I will be renaming this to “ListRow”.

Microsoft Power Platform: Power Automate - List rows present in a table

Let’s add another Step. Search for “Initialize Variable”. This is where I would need to add a formula randomize a row to give me the result into a variable.

Give your variable a name, type, and value.

Name: RandomDadJoke
Type: Integer (Since we’re randomizing the row number, this would be an integer)
Value: Expression in randomizing the value:
- rand(0,int(length(body('ListRow')?['value'])))

Microsoft Power Platform: Power Automate - Initialize variable

Now that we have the variable set, let’s go ahead and add another step to compose the result into something readable. Search for “Compose”, which is a data operation. And I need to add another expression only to return the value of the row from the variable.

body('ListRow')?['value']?[variables('RandomDadJoke')]

Microsoft Power Platform: Power Automate - Compose

The last step is passing the value from Compose to Power Virtual Agent in a form of a text. In this example, I gave it a name of “DadJokeOutput” and the response from “Compose”. But I need to adjust how it is presented because I only want the value from a single column which is “DadJokes”. So I used an expression: