Returning values from PowerBI using bots and Power Automate

Knowledge Level: Advanced

Purpose

In this tutorial, a bot user can trigger a specific query and return a relevant value from a PowerBI dataset.

When a bot user sends an inquiry, we will use a webhook to trigger a Power Automate scenario to run a DAX query on a PowerBI dataset and return the queried value to the bot user via our bot.

Prerequisite

For this tutorial we will need:

• A bot
• A Power Automate account (Paid)
• A PowerBI dataset
• White chocolate mocha (iced or hot depending on the weather)

Setting up the bot

To setup this bot, we will need two message parts:

• A welcome message, asking the user if they would like to pull metrics from PowerBI
• A confirmation message that the user's request is being queried. This is also the message where we will trigger our webhook

Setting up Power Automate flow

First we need to setup a webhook, please follow this guide to setup and configure a webhook in Power Automate -> Click here to access the guide

After setting up our webhook in Power Automate, we need to configure the query we will execute on Power Automate in the PowerBI connector. Start by adding a new module to your flow and look for 'Run a query against a dataset'

In this module, choose a relevant Workspace and Dataset from the drop down menus.

PowerBI uses DAX (Data Analysis Expressions) to retrieve specific values from its datasets. For more information, please click here.

In this example, we are extracting a sales figure from our dataset. We will use the following query:

EVALUATE
  ROW(
  "Sales__", 'SalesFact'[Sales $]
)

The final configuration of the module should look as follows:

If you execute the flow now, you will notice that the output from the PowerBI connector looks like this:

If we try to use this output in our webhook response to post the sales figure to a bot user, it will show up like this:

As you can see, the information being passed via the bot is not properly formatted. We definitely don't need [Sales__] in the bot response, instead we only need the sales figure.

To format this properly and only extract the numeric value of the sales figure, we will pass the output through a 'Parse JSON' module.

For the schema part in this module, copy the output from the previous PowerBI connector module and paste it in the text box that comes up after clicking 'Generate from sample'.

Setting up our response to the bot user

We are at the final step of the flow, here we will configure our response to the bot user with a text message and the numeric output we extracted from PowerBI. Start by adding a webhook response module by searching for 'Response' module:

We will use a simple text response JSON element in the body section of this module. Please use the following code:

{
  "recipient": {
    "id": "@{triggerBody()?['fbuser']?['fbid']}"
  },
  "message": {
    "text": "The total value of Sales is $@{body('Parse_JSON')[0]['[Sales__]']} "
  }
}

You can copy and paste the above code or modify the text to your liking. Please be aware, in order to extract the numeric sales value we have used a custom expression in Power Automate. Below is the custom expression we have used:

Our flow configuration is now complete. Make sure to save the scenario and return to the bot to trigger the query.

If everything has been setup correctly, you should receive a response from the bot with the relevant metric.

Happy DAX-ing :)